0

I am trying to do autobackup macro and I'm using FileCopy function. For that the workbook for backup must be closed or opened read-only (win permission).

My thought was to use another workbook "autosave-handler" to run macro to close backuping workbook "workplan", FileCopy to new dir with current date and then open "workplan" while finally closing "autosave-handler" workbook.

My problem is that when i run macro in "workplan.xlsm" through "application.run 'autosave-handler.xlsm'!macro.name, some_args (one arg. is "workplan.xlsm" as workbook for closing)", it is somehow stopped when the "workplan.xlsm" is closed for to be ready for copying. I cannot run macro further after closing the workplan.xlsm

I even tried to do some helping sub just in case it don't like caller to be closed, so i copy argument and mostly just call another sub inside sub. Dunno if it's for something in fact. Otherwise I'm not sure how to realiably indetify the backuping workbook and close it for it to be copied.

Maybe I'm on bad track with filecopy and there are easiest way.. Anything that could help me to solve auto-backups is welcome. I'm thinking of doing it this way for little flexibility, but I know nothing.

I'm running this in "workplan.xlsm":

Application.Run "'" & save_WB.Name & "'!" & "autosave", ThisWorkbook, ThisWorkbook.path, save_WB.path

where save_WB is "autosave-handler.xlsm"

for sub in "autosave-handler.xlsm":

Sub autosave(original_WB As Workbook, original_path As String, new_path As String)

I cannot use workbook.saveAs function as I have many hyperlinks to different workbooks and when used .saveAs, it ruined them all as I'm changing path by SaveAs. I'm also backuping these workbooks, but it is not problem right now as they are all closed when macro is triggered in main workbook "workplan".

Much thanks, sry for my English,

Kraapt

Kraapt
  • 1
  • 2
  • If you close the original code workbook, the `ThisWorkbook` object variable that you passed will no longer be valid and your called code won't be able to use it. That may or may not be your problem as you haven't posted the called code. I'd suggest it's safer to use something like `application.ontime` to schedule the `autosave` macro you want to run, then close the workbook you want backed up. – Rory Sep 23 '22 at 09:42
  • @Rory Thank you for your answer. If I get the method right, it will still keep my WB open when starting macro. I tried to lock `ThisWorkbook` information with variables but with no success. Somehow I missed workbook.changeFileAccess method, which help me a lot. I forgot to mention earlier that I was unable to open WB in read-only mode with VBA for some reason. I searched for 2-3 methods with no success, but the ChangeFileAccess did the work. /So now I dont need "autosave-handler" nor closing "workplan". It may not seem, but your suggestions get my indirectly on the right track. Thanks! – Kraapt Sep 23 '22 at 11:41

0 Answers0