Kinsy
asked on
Whats wrong with this ADO Connstring? Excel/VBA
Hi,
Got a Excel spreadsheet here. Its password protected on a network drive.
Now ive read the MS article about password-protected Excel spreadsheets - and i have the Excel file opened. This according to Q211378 allows you to create datasets of data in a password protected Excel spreadsheet. However, i still get the 'Could not Decrypt File' message pop up when i try to access it in VBA via ADO.
Im using Access 2000 and im trying to populate a listbox with some data btw.
Set dbexcel = New ADODB.Connection
dbexcel.Open "DRIVER=Microsoft Excel Driver (*.xls);DBQ=" & strFileName & ";ReadOnly=1;PageTimeout=5
Any ideas?
Cheers
Ross/Kinsy
Got a Excel spreadsheet here. Its password protected on a network drive.
Now ive read the MS article about password-protected Excel spreadsheets - and i have the Excel file opened. This according to Q211378 allows you to create datasets of data in a password protected Excel spreadsheet. However, i still get the 'Could not Decrypt File' message pop up when i try to access it in VBA via ADO.
Im using Access 2000 and im trying to populate a listbox with some data btw.
Set dbexcel = New ADODB.Connection
dbexcel.Open "DRIVER=Microsoft Excel Driver (*.xls);DBQ=" & strFileName & ";ReadOnly=1;PageTimeout=5
Any ideas?
Cheers
Ross/Kinsy
ASKER
Thanks for replying.
I have just tried the OLEDB Jet Provider, and got the same error message.
Connection string:-
dbexcel.Open "Provider=Microsoft.Jet.OL
"Data Source=L:\GALA BOM WIP.XLS;" & _
"Extended Properties=""Excel 8.0;"""
The Excel spreadsheet is on a network drive, could this be an issue?
I have just tried the OLEDB Jet Provider, and got the same error message.
Connection string:-
dbexcel.Open "Provider=Microsoft.Jet.OL
"Data Source=L:\GALA BOM WIP.XLS;" & _
"Extended Properties=""Excel 8.0;"""
The Excel spreadsheet is on a network drive, could this be an issue?
ASKER
Another thing to note is that I am trying to open a connection from Access 2000, not another Excel file. However as they both use VBA i didnt think this would be a problem.
looks like we've got a limitation here:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;211378
- rael
http://support.microsoft.com/default.aspx?scid=kb;EN-US;211378
- rael
ASKER
Thats the same article i mentioned id read at the beginning of the question, rael.
As i said, i have the Excel spreadsheet open and have entered the password, but i still cannot get ADO in Access to access it.
As i said, i have the Excel spreadsheet open and have entered the password, but i still cannot get ADO in Access to access it.
>>The Excel spreadsheet is on a network drive, could this be an issue?
No, it should not matter. Try this:
dbexcel.Open "Provider=Microsoft.Jet.OL
"Data Source=L:\GALA BOM WIP.XLS;" & _
"Jet OLEDB:Database Password=MyDbPassword", _
"Extended Properties=""Excel 8.0;"""
Leon
No, it should not matter. Try this:
dbexcel.Open "Provider=Microsoft.Jet.OL
"Data Source=L:\GALA BOM WIP.XLS;" & _
"Jet OLEDB:Database Password=MyDbPassword", _
"Extended Properties=""Excel 8.0;"""
Leon
ASKER
I only mention the Network drive as ive had a nightmare getting an ASP.NET page to access a networked Access file before.
dbexcel.Open "Provider=Microsoft.Jet.OL
"Data Source=L:\GALA BOM WIP.XLS;" & _
"Jet OLEDB:Database Password=<removed>", _
"Extended Properties=""Excel 8.0;"""
Still doesnt work. Im fairly sure you cant specify a password when connecting to Excel spreadsheets anyway (thats what the Support article says).
dbexcel.Open "Provider=Microsoft.Jet.OL
"Data Source=L:\GALA BOM WIP.XLS;" & _
"Jet OLEDB:Database Password=<removed>", _
"Extended Properties=""Excel 8.0;"""
Still doesnt work. Im fairly sure you cant specify a password when connecting to Excel spreadsheets anyway (thats what the Support article says).
Dumb question, but is L mapped to a different drive on the PC your testing than you thought? Try using the UNC network server name...
ASKER
It makes no difference, i have tried a copy locally and it still doesn't work.
Ive also tried the full server path, and got the same message.
Ive also tried the full server path, and got the same message.
ASKER
Requesting this question be closed an points refunded.
The Password is being removed from the Excel file; instead we are going to put the file in a folder only select people are allowed to access.
The Password is being removed from the Excel file; instead we are going to put the file in a folder only select people are allowed to access.
Kinsy,
Post your request to the Community Support area.
Post your request to the Community Support area.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJetExcel
Leon