Access External Excel Data

[trackstar2786]

Hello,

I'm linking external excel files to an internal db but the external file has dynamic headers.

Example. I always need to use Column R which is currently titled "September Activations" Next month Column R will read "October Activations".

Is there a way that when i update the linked excel file that it still references Column R even though it doesn't have the same name as before so that any queries using that file also auto update. I guess is there a way to rename it automatically without manually changing it to a standard header (i.e Previous Month Activations)

 

[Ranman256]

do you save each new workbook to the same name everytime, say: FileData.xlsx
or
do you relink to the new sheet everymonth?


can you :
open the new file given,
click a button to run macro,
this renames R column to StdHeader
then saves the workbook to FileData.xlsx

This fixes the field name problem, and prevents any relinking.

 

[Pat Hartman]

no. Perpetually changing column names are a problem.

Why not permanently rename the column to Prior Month Activations and put the date somewhere so you can figure out what the prior month is?

You could link to the sheet and say that it doesn't have headers. Then your query could alias the column names so they are not F1, F2, etc. The query would also have to ignore the actual header row by doing something like:

Where F1 <> "first column name"

 

[trackstar2786]

Pat, that was genius and worked great. Thank you.

 

[Pat Hartman]

You're welcome. I'm assuming you chose door #2.
I've had to fight with a lot of p*** poor spreadsheets over the years.