Solved

Whats wrong with this ADO Connstring? Excel/VBA

Posted on 2004-08-02
13
864 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

914 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now