As part of an Excel based scheduler that I have created, I am using the 'Application.Ontime' function in the following manner to execute macros at specified times
Sub Load
//snipped for brevity
Dim startName As String
startName = "'StartSub""" & filePath & """, """ & fileName & """, """ & macroName & """'"
Application.OnTime y, startName
End Sub
Sub StartSub(filePath As String, fileName As String, macroName As String)
Dim wb As String
wb = "'" & filePath & "'!" & macroName
Application.Run wb
Application.Workbooks(fileName).Close Savechanges:=True
End Sub
Testing and simple POC seems to have worked really well. The issue I am facing is that one of the spreadsheets is password protected. The problem is that password entry dialogue prevents the macro from executing which I guess is expected. There is no need for write access to the password protected workbook since the output and results are exported to several reports.
My question is how to overcome this and run macros from a password protected workbook using Application.Run?