Get data from password protected Excel file using

Question

I'm using this connection string to access excel(2007,2010) files

oleConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR = NO;IMEX = 1;'";

this work fine until i try to get data from protected List. I know the protection password. So how could i access protected list?

Answer 1

As per MSDN you can provide the credentials with the connection string User ID=UserX;Password=UserXPassword but this works for access

But

I'm afraid. You cannot open a connection to a password-protected spreadsheet unless you have already manually opened the spreadsheet in Excel . The described error is with the Excel ODBC provider, but the behavior is identical in the Jet 4.0 OLE DB provider. Your other option is to remove the password from the spreadsheet, and rely on some other security mechanism (like restricting permissions on the folder where the file resides) to control access.