Connect to password-protected Excel file

[rjoubert]

I have a VB.NET 2005 windows app that needs to read from and modify data in a password-protected Excel spreadsheet. I've done some googling and have modified my connection string according to my findings, but I still get an OleDB exception stating "Could not decrypt file."

Here's my connection string...
"provider=Microsoft.Jet.OLEDB.4.0; data source = '" & strExcelFilePath & "'; Jet OLEDBatabase Password=test; Extended Properties=Excel 8.0;"

Am I missing anything in the connection string (I have verified the password)? Any help would be greatly appreciated. Thanks.

 

[jebenson]

Well, this is probably not what you want to hear, but ConnectionStrings.com says the following about this:

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

http://www.connectionstrings.com/?carrier=excel

Sorry.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[rjoubert]

You're right, that's not what I wanted to hear. I've now started going down the path of opening the excel file, unprotecting it, adding data to it, then reprotecting it and closing it. But I can't seem to get that to work either. Here's my code...

Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook

objExcel = New Excel.Application

objWorkbook = objExcel.Workbooks.Open(strPath)
objWorkbook.Unprotect("test")
objWorkbook.Save()

AddData("One", "XXX")

objWorkbook.Protect("test")
objWorkbook.Save()
objWorkbook.Close()

objWorkbook = Nothing
objExcel = Nothing

My AddData procedure (which worked fine before this password-protection fiasco)...

Private Sub AddData(Sheet as String, Data as String)

Dim objConnection As System.Data.OleDb.OleDbConnection
Dim objInsertCommand As System.Data.OleDb.OleDbCommand

Try

   objConnection = New System.Data.OleDb.OleDbConnection(strPath)
   objConnection.Open()

   objInsertCommand = New System.Data.OleDb.OleDbCommand("insert into [" & Sheet & "$] values ('" & Data & "')", objConnection)
   objInsertCommand.ExecuteNonQuery()

   objConnection.Close()

Catch ex As Exception

   MessageBox.Show(ex.ToString)

Finally

   objInsertCommand = Nothing
   objConnection = Nothing

End Try