I am trying to query an excel document (2003 xls) with locked columns that is password protected using vb.net.
I have Office 2007 installed, but am hoping that having office isnt required because this needs to run on a server.
These are the connection strings I have been trying. Currently I am getting "Could not Descript File" errors. From googling it, it looks like either of these connection strings should work.
I saw some suggestions that involved using interop services and resaving the excel file without the password. That isn't an option since this needs to run on a server and can't excel installed or have the memory problems with opening and closing excel all the time.
Dim sConnectionSTring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\filename.xls;Jet OLEDB:Database Password=passwordforfile;Extended Properties=Excel 8.0;"
Dim sConnectionSTring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\filename.xls;Jet OLEDB:Database Password=passwordforfile;Extended Properties=Excel 12.0;"
Dim dt As New DataTable
Using objConn As New OleDbConnection(sConnectionSTring)
Using objCmd As New OleDbCommand("SELECT * FROM [Sheet1$]", objConn)
Using objAdapt As New OleDbDataAdapter
objAdapt.SelectCommand = objCmd