ADO connection string for Excel 1

[jbradleyharris]

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.

 

[gmmastros]

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

 

[tedsmith]

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)

 

[dilettante]

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

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:

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

... and then by extension:

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

... or even:

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 [tt]HDR=No[/tt] 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.

 

[jbradleyharris]

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.

 

[dilettante]

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.