Need excel macro to backup open file but with a different name each day 
Author Message
 Need excel macro to backup open file but with a different name each day

I need an Excel macro (Office 97/VB) that can do the following:
When a file is opened or closed (say once/day), copy the file to a backup
with a different filename based on the current date.
  Eg. if it's July 8, the file would be saved as c:\backup_1999_07_08
         if it's November 30, the file would be saved as
c:\backup_1999_11_30

I looked up the VB FileCopy statement but it says it will give an error if
the file
is open.  If I use File-SaveAs, then how do I return to the non-backup file
?

My other related problem is:  I can easily create a cell which contains the
text
to represent the filename (eg. "c:\backup_1999_07_11"), but how do I get
that
cell content to the macro ?

Thanks in advance for any help



Thu, 27 Dec 2001 03:00:00 GMT  
 Need excel macro to backup open file but with a different name each day
Hello ! I'm in a hurry so this is just sth to point you in the right
direction:

The original file is myfile, the name of the backupfile is stored in Sheet
Ark1 Cell A1:
This sub copies myfile as the name you enter in Ark1 Cell A1, closes the
file and opens the original file myfile.

Sub test1()
    bckname = Worksheets("Ark1").Range("A1")
    ActiveWorkbook.SaveAs Filename:=bckname
    ActiveWorkbook.Close
    Workbook.Open Filename:="myfile"
End Sub

To save the file in the correct directory you will have to include the path.
The sub might be beautified a bit but I think it will work. If you want to
run it when myfile is opened or closed you have to put it in a Auto_Open or
Auto_Closed macro.

I have to run !!

Regards
Knut Egil Sj?li


Quote:
>I need an Excel macro (Office 97/VB) that can do the following:
>When a file is opened or closed (say once/day), copy the file to a backup
>with a different filename based on the current date.
>  Eg. if it's July 8, the file would be saved as c:\backup_1999_07_08
>         if it's November 30, the file would be saved as
>c:\backup_1999_11_30

>I looked up the VB FileCopy statement but it says it will give an error if
>the file
>is open.  If I use File-SaveAs, then how do I return to the non-backup file
>?

>My other related problem is:  I can easily create a cell which contains the
>text
>to represent the filename (eg. "c:\backup_1999_07_11"), but how do I get
>that
>cell content to the macro ?

>Thanks in advance for any help



Fri, 28 Dec 2001 03:00:00 GMT  
 Need excel macro to backup open file but with a different name each day
Excel has a builtin command to save  a copy:

sStr = "c:\backup_" & format(date,"yyyy_mm_dd") & ".xls"
Workbooks("workbookname.xls").SaveCopyAs Filename:=sStr

HTH,
Tom Ogilvy

Quote:

>I need an Excel macro (Office 97/VB) that can do the following:
>When a file is opened or closed (say once/day), copy the file to a backup
>with a different filename based on the current date.
>  Eg. if it's July 8, the file would be saved as c:\backup_1999_07_08
>         if it's November 30, the file would be saved as
>c:\backup_1999_11_30

>I looked up the VB FileCopy statement but it says it will give an error if
>the file
>is open.  If I use File-SaveAs, then how do I return to the non-backup file
>?

>My other related problem is:  I can easily create a cell which contains the
>text
>to represent the filename (eg. "c:\backup_1999_07_11"), but how do I get
>that
>cell content to the macro ?

>Thanks in advance for any help



Fri, 28 Dec 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Import Excel file with varying day-to-day field names

2. Opening 2 excel file with same name in different folder

3. Creating macro for to open different excel files

4. single day calendar events become two day events when imported from another backup.pst file

5. when opening an excel file programatically, the newly opened file's Auto_Open macro runs

6. Need help building macro to open different tabels

7. Sorting Excel files by day of month and month name

8. Name of open workbook macro needed

9. Excel Saves file with a different name

10. I HAVE PUBLISHER '98, I NEED TO OPEN A DIFFERENT VERSION FILE


 
Powered by phpBB® Forum Software © phpBB Group