How to use Application.Run for a password protected workbook?

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 out开发者_如何学Goput 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?

It no workie. MS doesn't support it. You'll need to unprotect the workbook before you run it. Here is an example: http://www.ozgrid.com/forum/showthread.php?t=36816&page=1

Since the workbook is password protected, a password always is supplied when openign the file. In order to overcome this, I needed to store the passwords in my workbook. So the issue actually boiled down to opening a file with the correct password.

Sub StartSub(filePath As String, fileName As String, macroName As String)
    Dim wb As String
    wb = "'" & filePath & "'!" & macroName
    Dim scheduledWb As Workbook

    Dim pwd As String
    Dim saveChange As Boolean
    pwd = GetPassword(fileName) ' method that extracts correct password from somewhere
    If Len(pwd) > 0 Then
        Set scheduledWb = Workbooks.Open(fileName:=filePath, ReadOnly:=True, Password:=pwd)
        saveChange = False
    Else
        Set scheduledWb = Workbooks.Open(fileName:=filePath)
        saveChange = True
    End If

    Application.Run "'" & fileName & "'!" & macroName
    Application.Workbooks(fileName).Close Savechanges:=saveChange
End Sub