Can we have 2nd or other Excel row as Column Names

I learnt from connectionstrings.com that "HDR=Yes" in Connection String for Excel indicates that 1st row is not values but Column Names.

I have a requirement where I need to consider 2nd row as the Column Names. Can I achieve this by modifying the connection string? If no, please help me with the work arounds.

Thanks a lot.

3 answers

Hi
Im not aware of any solution to your situation, please get back if you find a solution somehow. I bet many people run into this. Is it possible to open with the first row as headers and then jump the first (header-) record when reading.
Or maybe open with HDR=NO and then retrieve data based on column index instead of column names, jumping 2 rows in your scenarion.
Let me know, Thanks!

One simple work-around would be to name the excel table as a named range and use the name in the connection string instead of the name of the sheet.

However, that method has a limitation: when you add rows to the excel table through an ADO or DAO command, the rows will be added but the named range will not be extended to cover the new rows. As a result, if you query the excel table again after the addition of new rows, the new rows will not be fetched.

The limitation cannot be overcome by using "dynamic named ranges", where "offset" excel worksheet formula is often used to automatically update the named range whenever number of rows changes; database connections are blind to such named ranges.

You can programmatically open the workbook after adding rows and update the named range's refer-to address accordingly.

I hope this helps.

Thanks a bunch! As you said the solution does has a limitation but it should not matter for read-only tables at least. I also learned a small lesson about "Dynamic Named Ranges" too. Thanks again!!