Solved

Whats wrong with this ADO Connstring? Excel/VBA

Posted on 2004-08-02
13
856 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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 …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
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…

746 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

11 Experts available now in Live!

Get 1:1 Help Now