Create backup via VBA when opening Excel workbook

I've cobbled this together from a few different references and it's still not working quite right.

1) Backup an Excel workbook when it is opened automatically.

--seems to work with Active.workbook.SaveCopyAs

2) Prevent the backup workbooks from making copies of themselves if they are opened.

--Gives an error; seems like the IF statement isn't working.

3) Also seeking a way to remove the password protection from the original file when a backup is made. The backups do not need to be password protected.

--I know ActiveWorkbook.SaveAs has an option to set the password to "". However, using the Save.As method renames the original file and saves it to the backup location, which is not what I'm going for. Is there some workaround to make SaveAs work more like SaveCopyAs?


The backup file path is N:\Receipts\Receipt Tracker Backup\

My current code:

Please Login or Register  to view this content.

Thanks. I don't know why I didn't think of that; too wrapped around the axle of the way I was trying it. #2 solved.

Looks like a simple method for the #3 is wishful thinking. I'd rather not have to go in and open every backup. Maybe a method will pop up.

Still working on #3 --

a) I decided to try using SaveAs 2x. Once to save to the backup location with the name Date_OriginalFilename. This saved the open file to the backup location & renames it. I don't want to work in the backup file or location. So then I use SaveAs a second time to save the file back to the original file location under the name OriginalFilename.

I seem to have trouble on the second SaveAs with attempting to store the original file name and file location as variables. What am I doing wrong?

b) I want to keep the original file password protected and backups unprotected. How do I retrieve the original password as a variable, store it and reapply it on the second SaveAs?

The original file path is N:\Receipts\
The backup file path is N:\Receipts\Backup_Tracker\

Updated code:

Please Login or Register  to view this content.

It still dies on the second SaveAs. I suspect it's because the 1st saveAs puts the open file into the backup folder. Then the second SaveAs cannot get out of it to the original location.

As for the password, it's my spreadsheet, I created the password. I'd like for the spreadsheet to outlive me. The chances of the next person to inherit this spreadsheet knowing VBA are slim. I don't want to display the password. I just want VBA to temporarily store the password, so that it can reapply the password on the second SaveAs. The next person won't know to go into VBA to edit the password.

I'm trying to make it seamless and automatic with minimal user input.

Windows 7
Excel 2010
Network locations
Original file location N:\Receipts\
Backup file location N:\Receipts\Backup_Tracker\

It saves the first time on the network.
It just doesn't save the second time. I'm thinking the location and name variables need to be adjusted....somehow.