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.

 

[trackstar2786]

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