Archiving Macro

[jumex]

Right now I have an excel workbook and I have hammered out all the bells and whistles except one.

This file gets changed on a daily basis and the boss wants it saved to 2 locations every day.

One is a location where it overrides the previous day’s version; this is easy enough to do.

The other save needs to include the current date in the name of the save for example workbook09182006 this then gets saved to an archive folder.

This whole thing needs to be automated I created a "home made" wizard with VB forms. One of the command buttons, on one of these forms is “Save and Archive”. I just can’t figure out how to get it to apply a new date (the current date) using the same button every day.

Any help would be appreciated.

 

[DirkStruan]

jumex

to obtain today's date within your module, try

Format(Now(), "mmddyyyy")

you should be able to include this into your save macro as the extension to the filename

HTH

Dirk

 

[Gavona]

This code snippet shows how I create an archived copy of a file. In my case the user is prompted for a date with the default being todays. And I store this in cell A1 on the workbook. The filename is also stored in cell A2 on the workbook. The path for the current copy is hard coded into a variable "MyPath" and the the archive folder is a subfolder of this

Title = "On what date was this hierachy info taken from SAP?"
Range("A1").Formula = "'" + InputBox(Title, Default:=Format(Date, "DDMMMYY"))
.
.
With ActiveWorkbook
'save a copy incorporating the date in the archive
    .SaveCopyAs Filename:=MyPath & "Old Hierachies\" _
      + Range("a2").Value + " " _
      + Range("A1").Value + ".xls"
'and save as the latest version with standard name
    .SaveAs Filename:=MyPath + Range("A2").Value + ".xls"
    .Close
End With

Gavin

 

[jumex]

Works perfectly, thanx guys.

I have more more "feature" I would like to add if at all possible.

This document actually gets saved 3 times a day. Once for each shift, Days, Swings and Graves. After the first shift does the save everyone else gets the "Do you want to replace this file?" message. Is there any way I can get it to just over right that certian file with out prompting?

 

[DirkStruan]

jumex

Insert

Application.DisplayAlerts =False

in your code before you save the file and then return it to True before you exit the macro.

That should be it.

Dirk

 

[jumex]

Thank you, I dont know how many hours of research you just saved me.

 

[DirkStruan]

Jumex

I cant take the credit there, Tony Jollans pointed it out in thread68-1276655 after I'd suggested some ugly code as a fix to a problem.

If Tony's reading - it did stick!

Dirk