Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Whats wrong with this ADO Connstring? Excel/VBA

Posted on 2004-08-02
13
Medium Priority
?
874 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
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…
Suggested Courses

618 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