Create Backup File Within A Macro

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?

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?

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.

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".

Tested.

Please Login or Register  to view this content.