I need to be able to handle Excel files versions 97 - 2010 and also those with and without header info.
Don't need help with connection strings : that works.
What I do need help with is querying worksheets that do not have header information i.e. no descriptor of a column's data in the first row. How do I refer to these columns in the OLEDB query? I've tried the following
"Select [Sheet2$A] from [Sheet2$]" //selecting Column A
"Select [Sheet2$A$] from [Sheet2$]" //selecting Column A
Refer to the columns as F1, F2, ... Fn, numbering from the first selected column.
SELECT * FROM [Sheet1$G5:Z12]
g5 = row 1, column F1.
So:
SELECT * FROM [Sheet1$G5:Z12]
WHERE F2 = 'abc' AND F3 > #2011/01/31#
This post put me onto the answer, Thanks! Just a minor clarification that kept me guessing for a while: In the above SELECT * FROM [Sheet1$G4:Z12] note that it is the sheet name not the number that goes into the part "Sheet1" E.g. "Changes", the $ that follows it vital and then no other $'s as might be used for excel ranges. In my case the full string reads: SELECT * from [Changes$A3:Z13]
