Create Backup File Within A Macro

**nebb** · 07-08-2007, 08:51 PM

I am using a macro to update a database. At the end of the update, I would like for the macro to automatically save the entire excel workbook (name of workbook is ‘EARN’.xls) with an extension of .bak instead of .xls with the file saved to a specific folder(named BACKUP) within 'My Documents'. Does anyone have a routine that I might use to accomplish this that I can slip into this macro?

**Tarball** · 07-08-2007, 09:39 PM

Here is a simple routine that I use

config.defaultOutputPath is just a variable that sets the default path to where I want the file saved (use the full path, for example C:\temp\)

sourceFile is the orginal file name and path

    Dim fs As FileSystemObject
    Dim bkupFile As File
    Dim fname as String
    
    fname="EARN.xls.bak"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set bkupFile = fs.GetFile(sourceFile)
    'bkupFile.Copy config.defaultOutputPath, True
    bkupFile.SaveAs config.defaultOutputPath & fname, FileFormat:=xlCSV

'Memory Cleanup
    Set bkupFile = Nothing
    Set fs = Nothing

**nebb** · 07-09-2007, 07:59 PM

When I try to run the recommended routine, I get a compile error on the first line with the comment "User defined type not defined". What need be done to correct this?

**Tarball** · 07-10-2007, 06:52 AM

Hello -

I forgot to mention that you need to add a reference to the Microsoft Scripting Runtime library. To do this:

1. Open up your excel worksheet
2. Open up the VBA editor
3. Select Tools-->References
4. Locate the "Microsoft Scripting Runtime" library
5. Check the box next to the library
6. Select OK
7. Save

Once you do this Excel will be able to see the objects referenced in the code.

**nebb** · 07-10-2007, 08:40 AM

Microsoft Scripting Runtime library is now checked. I am now getting a compile error on the line that starts bkupfile.saveas
The .saveas is highlighted and the compile error reads "Method of Data Member not found".

**Tarball** · 07-10-2007, 09:41 AM

Tested.

Private Sub CommandButton1_Click()

    Dim fs As FileSystemObject
    Dim bkupFile As File
    Dim FileNameAndPath As String
    
    FileNameAndPath = "C:\Temp\foo.xls"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Application.ActiveWorkbook.SaveAs "C:\temp\bar.xls"
    Set bkupFile = fs.GetFile("C:\temp\bar.xls")
    bkupFile.Copy FileNameAndPath, True

    'Memory Cleanup
    Set bkupFile = Nothing
    Set fs = Nothing
End Sub