Solved

Whats wrong with this ADO Connstring? Excel/VBA

Posted on 2004-08-02
13
871 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

688 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