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:
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 adLockOptimisti c)
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:
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)
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 adLockOptimisti c)
Code:
rs![Field name 1]=rs![Field name 1]