Excel Connection: multiple-step data generated errors - .xls file

I am trying to connect XLS file and below is my connection string.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 12.0 xml;IMEX=1;HDR=Yes;ReadOnly=1;"

Able to get my data bypassing the connection errors but always getting the "multiple-step data generated errors" while connecting but I can get data if I omit this error.
Help me to remove this connection error.

Also tried with CursorLocation = 3 but no luck.

1 answer

Try this:
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & file & ";" & _
"Extended Properties=" & Chr(34) & "Excel 8.0;IMEX=1;HDR=YES;ReadOnly=1;" & Chr(34)

You should be using excel 8.0 for a .xls file and excel 12.0 for .xlsx file I think. I might be wrong about that though. If you have more than 1 item in the Extended Properties you need to wrap them in quotes chr(34) otherwise the provider cannot tell the difference between extended properties and other items in the connection string. Don't know if that makes sense but if you look at it like this provider = one thing
data source = one thing , extended properties = " a bunch; of ; Things; that should be other properties if not wrapped in quotes.

I hope it helps.