Solved

Whats wrong with this ADO Connstring? Excel/VBA

Posted on 2004-08-02
13
868 Views
Last Modified: 2013-12-25
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=5000;MAXSCANROWS=0;IMEX=1;"

Any ideas?

Cheers

Ross/Kinsy
0
Comment
Question by:Kinsy
13 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 11695926
0
 

Author Comment

by:Kinsy
ID: 11701397
Thanks for replying.

I have just tried the OLEDB Jet Provider, and got the same error message.

Connection string:-

dbexcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "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?
0
 

Author Comment

by:Kinsy
ID: 11701461
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.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 3

Expert Comment

by:realrael
ID: 11702746
looks like we've got a limitation here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;211378

- rael
0
 

Author Comment

by:Kinsy
ID: 11702777
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.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11703769
>>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.OLEDB.4.0;" & _
           "Data Source=L:\GALA BOM WIP.XLS;" & _
           "Jet OLEDB:Database Password=MyDbPassword", _
           "Extended Properties=""Excel 8.0;"""

Leon
0
 

Author Comment

by:Kinsy
ID: 11704232
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.OLEDB.4.0;" & _
           "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).
0
 
LVL 1

Expert Comment

by:shendr8086
ID: 11728866
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...
0
 

Author Comment

by:Kinsy
ID: 11733629
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.
0
 

Author Comment

by:Kinsy
ID: 11774366
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.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11774472
Kinsy,

Post your request to the Community Support area.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12096735
PAQed, with points refunded (125)

modulo
Community Support Moderator
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question