Unable to read columns in an Excel spreadsheet

[tommys]

Hi

I am attempting to connect to an excel file to check if the column names is correct (to validate that it is the file I expected) using the following code:

dim cn as adodb.connection
dim rs as adodb.recordset
cn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\..\file.xls" & _
";Extended Properties=""Excel 8.0;HDR=YES"""
rs.open "SELECT * FROM [Sheet1$]",cn,adOpenStatic,AdLockReadOnly
msgbox rs.fields(0).name 'Success
msgbox rs.fields(1).name 'Error (Item cannot be found in the collection corresponding to the requested name or ordinal)
 

Now if I open the file in Excel and makes any change (like adding a character and then deleting it again), save and close file. and then run the function again. Both the msgboxes work as expected.

The Excel file in question is rather large (4 full worksheets of est. 65500 rows each and a few thousand rows on the 5th worksheet)

Any suggestions on how to make this work?
Manualy opening, changing and saving the file each time is not an option.

Have tried:
Change and update the file via code(opening the rs with adLockOptimistic)

 rs![Field name 1]=rs![Field name 1]