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:
Expand|Select|Wrap|Line Numbers
- 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)
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)
Expand|Select|Wrap|Line Numbers
- rs![Field name 1]=rs![Field name 1]