Solved

excel ADO connection string

Posted on 2014-03-05
7
889 Views
Last Modified: 2014-03-11
Hi All,
when attempting to read data from and encrypted xlsx using:
dbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & SourceFile & "';Extended Properties=Excel 12.0;"
and dbConnection.Execute.

I get an error:
Error -2147467259, External table is not in the expected format. occurred in GetRecordsetFromClosedWorkbook

The error goes away when the file is not encrypted.

adding ;Database Password =test; to the connection string gives a different error:

Error -2147467259, Could not find installable ISAM. occurred in GetRecordsetFromClosedWorkbook

When I use Excel Open (with password) and copy/paste methods all is good

How does one add Excel encryption password to the ADO connection string?

thanks
0
Comment
Question by:COACHMAN99
[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
  • 4
  • 3
7 Comments
 
LVL 42

Expert Comment

by:pcelba
ID: 39908656
Password parameter does not help here but you may use the work around described here: http://www.connectionstrings.com/how-to-open-password-protected-excel-workbook/
You just have to change JET.OLEDB for ACE.OLEDB etc.

Another possibility is to save decrypted workbook into a temporary file and then open it via ADO.

Yes, Microsoft could do it better...
0
 
LVL 7

Author Comment

by:COACHMAN99
ID: 39909870
Hi pcelba
thanks for the input. Unfortunately there are thousands of workbooks in this situation and inputting passwords at a prompt isn't an option. (and when I use workbook.open with a password param, to read data, I am able to work without any prompts; just wanted to use ADO).
cheers,
0
 
LVL 42

Assisted Solution

by:pcelba
pcelba earned 250 total points
ID: 39910388
It is not necessary to ask user for the password but you may provide it as a parameter when opening the Excel workbook.

I don't know the exact syntax but you may use something like this:
Dim xl As New Excel.Application
Dim xwb As Excel.WorkBook

Set xwb = xl.Application.WorkBooks.Open "YourXLSXfile", , , , "password", "password"

and now you should be able to use ADODB connection without any restrictions.

I've tested FoxPro variant of above code and it opens password protected workbook without user intervention.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 7

Author Comment

by:COACHMAN99
ID: 39910421
Hi,
I wasn't asking a question; just informing you of how I currently work around the password issue. (Workbooks.Open("L:\Backup\2014\PGT\BE.xlsx", , , , "test")
thanks
0
 
LVL 42

Expert Comment

by:pcelba
ID: 39910474
Yes, my English is not so good... you wrote "just wanted to use ADO" which I translated as you cannot use ADO even after opening the workbook via OLE automation...
0
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 0 total points
ID: 39911400
APPARENTLY THE BOTTOM LINE IS:
1. One cannot use a password in an ADO connection to an encrypted Excel workbook.
2. Instead, one must use the Excel 'workbook.open' functionality (which makes use of the optional password).

thanks pcelba for input.
0
 
LVL 7

Author Closing Comment

by:COACHMAN99
ID: 39919998
only workable option.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VS2015 Redefinition errors 4 89
Excel Sheet Data Finding 14 35
Help to break down spreadsheet 3 38
keep the cell in the formula in excel 2013 5 25
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Viewers will learn what comprises a theme in Excel 2013, as well as how to customize them.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

756 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