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.