Expected table is not in the expected format - ADODB Connection to Read Only Excel file

Hi,

A very strange issue noticed but dont know why no one on the net ever noticed it. I can see many of the users facing the same issue but concluded a very different reason for it and end of the day accepted that there is no solution to it.

Here is the issue I'm facing for all those VBA experts who might have faced this issue and have the solution to it.

I have a very small data in Excel and I'm connecting the same excel file using ADODB connection (I know there are various means of connecting excel and fetching records from the excel however for some reason i would like to stick to ADODB and would like to know if there is a solution to the issue raised).

So, when i connect the excel file using ADODB connection, i'm able to connect it with no issues.
Now, when i make the file as read-only by clicking on properties of the excel file, selecting it as read-only and hitting ok, i face an error stating "External table is not in the expected format".

I have a necessity to keep the file as read only and dont want to us "save as" option even on a temp basis as a workaround.

I repeat, the same code runs perfectly fine when the file is in read-write mode, only when i make the excel file as read only the issue begins.

This happens with ace.oledb.12.0 as well as jet.oledb.4.0

Any suggestions pls.

Rgds
Roshan

Hmm, its kind of hard to say without looking at your code.
Is there any way you could post a sample of the workbook/code?

Without being too much confident, I would still say the issue is not in the code however something else. If you can connect Excel as a database using ADODB, you can simple create the simplest table in Sheet1, try connecting it using ADODB connection. You will be able to do the same. Now, just make it read-only using the properties windows of the file and then try connecting it. You will get the error I'm facing.

This error is all over the internet with no solutions.

Without being too much confident, I would still say the issue is not in the code however something else. If you can connect Excel as a database using ADODB, you can simple create the simplest table in Sheet1, try connecting it using ADODB connection. You will be able to do the same. Now, just make it read-only using the properties windows of the file and then try connecting it. You will get the error I'm facing.

This error is all over the internet with no solutions.

Hi,
I'm getting the exact same error. You got any solution for this?