Open a password-protected Excel workbook

If you try to open a password-protected Excel workbook from Access, you get an error message “Could not decrypt file”. Adding a Password= value to your connection string won’t work either. You could open it as an Excel application, but then you’d have to learn about the Excel object model and all you want to do is read it row-by-row as you would any unprotected Excel file.

To get around this problem, first open the Excel workbook using GetObject() and then open it using the connection / recordset approach that you want to use. The GetObject() open prompts your user for the password, and then you are free to open the file using your preferred approach. For example:

    'Open the Excel workbook to prompt for the password
    Dim xl As Object
    Set xl = GetObject(CurrentProject.Path & "\" & "MASTER JAN 2009.xls")
    'Now open the workbook to read/write/whatever
    Dim cnn1 As New ADODB.Connection
    Dim rst1 As ADODB.Recordset
    Dim strExcelName As String = "MASTER JAN 2009.xls"
    Dim strWkBkName As String = "[MASTER 2008$]"
    cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    "Data Source=" & CurrentProject.Path & "\" & strExcelName & ";" _
    "Extended Properties=""Excel 8.0;"";"
    Set rst1 = New ADODB.Recordset
    rst1.Open strWkBkName, cnn1, , , adCmdTable
    Do Until rst1.EOF
        'process your data here
        rst1.MoveNext
    Loop
    

A simple hack lets you work with password-protected Excel spreadsheets and avoid the frustrating “Could not decrypt file” error message. Of course, your user has to know the password or this won’t work.