INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ADO connection string for Excel

ADO connection string for Excel

(OP)
I am having a problem using ADO open an Excel spreadsheet. If I add the extended property parameter "HDR=NO" I get an "Error Number:-2147467259, Could not find installable ISAM." message. If I leave it off the connection succeeds but I lose the first row because there is no header row in the spreadsheet. The spreadsheet is created by an external process so adding a header row is not really an option. I assume I am doing something wrong but for the life of me I can't figure out what. Here are my two connection strings:

This one works -> strMyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strSourceFile & ";Extended Properties=Excel 12.0 Xml"

This on fails -> strMyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strSourceFile & ";Extended Properties=Excel 12.0 Xml;HDR=No"

Other than that what I'm doing is as simple as can be:

Dim cn as ADODB.Connection
Set cn = New ADODB.Connection
cn.Open strMyConnect

I have references to the Microsoft ActiveX Data Objects 2.8 Library and the Microsoft Excel 14.0 Object Library in my project.

Any suggestions would be greatly appreciated.

RE: ADO connection string for Excel

What is the file extension on your excel file?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: ADO connection string for Excel

If all else fails can you use a routine to write(insert) a blank first row to the file before you read it? (or to a copy if you don't want to disturb the original)

RE: ADO connection string for Excel

You could put in a little more effort to help tell ADO what you are trying to do.

CODE

strMyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
             & strSourceFile _
             & ";Extended Properties=Excel 12.0 Xml" 

... works by luck when it works at all. In order to help ADO's connection string parser you really want to say:

CODE

strMyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" _
             & strSourceFile _
             & "';Extended Properties='Excel 12.0 Xml'" 

... and then by extension:

CODE

strMyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" _
             & strSourceFile _
             & "';Extended Properties='Excel 12.0 Xml;HDR=No'" 

... or even:

CODE

strMyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" _
             & strSourceFile _
             & """;Extended Properties=""Excel 12.0 Xml;HDR=No""" 

... which is equivalent since ADO will accept either string literal delimiter.

The issue is that the parser has no problem with unquoted values as long as they do not contain spaces or semicolons, but once you start throwing those into the picture it has to try a lot of backing and filling to try to guess what you really intended.

In your case you did not make HDR=No part of the Extended Properties value but a connection property on its own and there isn't any connection property "HDR" so it falls over trying to interpret that as some Installable ISAM designator.

RE: ADO connection string for Excel

(OP)
Dilettante, you hit the nail on the head. I had tried putting double quotes around the extended properties but that generated a syntax error (expected end of statement) and it never occurred to me to that I needed to double up on the double quotes. I find your example with the single quotes more pleasing to my eye and it works perfectly.

Many thanks and a star for your efforts. And a warm thanks to gmmastros and tedsmith as well.

RE: ADO connection string for Excel

Glad it helped!

For more details on how ADO parses connection strings programmers could read Connection String Syntax, but most casual coders are just looking for a copy/paste answer.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close