Connection to .xlsx HDR=Yes does not work

When I use the following connect string everything works fine:
sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & file & ";" & _
"Extended Properties=" & "Excel 12.0 Xml;"

If I try to use the following from this website it throws an error:
sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & file & ";" & _
"Extended Properties=" & "Excel 12.0 Xml; HDR=YES;"

Error is err.Num = -2147467259 err.description is "Could not find installable ISAM"

Does anyone know why this is happening I have also tried using the following:
sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & file & ";" & _
"Extended Properties=" & "Excel 12.0 Xml; HDR=YES; IMEX=1;"

I get the same error. Can someone please help?

Thanks,
Clint

1 answer

Hello to anyone else having this problem I found the solution on stack exchange:

If you use more than 1 extended property then the value tokens must be quoted, otherwise there is no way for the driver to distinguish them from the other non-extended properties in the connection string;

Example:
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & file & ";" & _
"Extended Properties=" & "Excel 12.0 Xml; HDR=YES;"

Will not work but if you do this:
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & file & ";" & _
"Extended Properties=" & Chr(34) & "Excel 12.0 Xml;IMEX=1;HDR=YES;" & Chr(34)

It now works.

I hope this helps others.