Hello all!
I have run automated VBA scripts for some years without much issues. Currently in Windows 10 and Office 365.
Windows task scheduler -> PowerShell script -> Excel + VBA code
There are various macros and workbooks, but most launch some PowerQuery connection, process data and might send out emails. For archiving and tracing reasons, macros usually end with ThisWorkbook.Save statement before closing.
For the past 3 months or so I have started to experience an annoying issue that saving the workbook causes macros to be "hidden" in the workbook. This in turn causes the situation where VBA code can't be launched the next time (targeted macro not found). Also, if I open the workbook manually and check "View" -> "Macros" I get this:
So, I can't even run them manually. The macros are somehow deleted, right?
Not quite. If I enter VBA editor (Alt + F11) I see all the expected code and modules - nothing is technically gone, just somehow not accessible after saving.
If I add a new module in VBA editor and copy all the existing VBA code there and then save the workbook, macros are "visible" again and possible to launch either manually or via automated script. I haven't seen a pattern of when it happens and to what kind of workbook it happens. While the macros are launched daily, this happens something like once a week. Also, this always happens when Excel macros are processed with an automated script - I have never experienced this when saving a workbook manually. With that being said, I don't really have a reason to save those workbooks often manually, so the sample size is low.
While I can "fix" the situation manually the main problem is that it breaks the automated flow which should really work consistently and independently. Any similar experiences or what could be the culprit? Or might there be a way to automatically fix that kind of workbook?
On the side - in some cases I have been able to do a workaround with ThisWorkbook.SaveCopyAs, but in some situations I can't avoid ThisWorkbook.Save, leaving my automated flow vulnerable. I have recently turned from VB Script to PowerShell, but the issues started already with VB Script. The same also happens in 2 different computers (similar setup though).
I have run automated VBA scripts for some years without much issues. Currently in Windows 10 and Office 365.
Windows task scheduler -> PowerShell script -> Excel + VBA code
There are various macros and workbooks, but most launch some PowerQuery connection, process data and might send out emails. For archiving and tracing reasons, macros usually end with ThisWorkbook.Save statement before closing.
For the past 3 months or so I have started to experience an annoying issue that saving the workbook causes macros to be "hidden" in the workbook. This in turn causes the situation where VBA code can't be launched the next time (targeted macro not found). Also, if I open the workbook manually and check "View" -> "Macros" I get this:
So, I can't even run them manually. The macros are somehow deleted, right?
Not quite. If I enter VBA editor (Alt + F11) I see all the expected code and modules - nothing is technically gone, just somehow not accessible after saving.
If I add a new module in VBA editor and copy all the existing VBA code there and then save the workbook, macros are "visible" again and possible to launch either manually or via automated script. I haven't seen a pattern of when it happens and to what kind of workbook it happens. While the macros are launched daily, this happens something like once a week. Also, this always happens when Excel macros are processed with an automated script - I have never experienced this when saving a workbook manually. With that being said, I don't really have a reason to save those workbooks often manually, so the sample size is low.
While I can "fix" the situation manually the main problem is that it breaks the automated flow which should really work consistently and independently. Any similar experiences or what could be the culprit? Or might there be a way to automatically fix that kind of workbook?
On the side - in some cases I have been able to do a workaround with ThisWorkbook.SaveCopyAs, but in some situations I can't avoid ThisWorkbook.Save, leaving my automated flow vulnerable. I have recently turned from VB Script to PowerShell, but the issues started already with VB Script. The same also happens in 2 different computers (similar setup though).
