Automatically Update a Linked Table Filepath

[newguy86]

Hello Everyone,
I have an Access DB that has linked tables to excel data that I'm trying to compare. When I setup the linked tables I made sure to name the excel files something relatively generic so that any new data that was generated could just overwrite the old data using the same file name.

Unfortunately whenever a new file is saved over the old one, the worksheet that the data is in has a different name than before. So I'm looking into ways to automatically relink the data and capturing the new worksheet name so that the system can run almost automatically.

Any suggestions?

Travis
Charter Media

 

[Darrylles]

newguy,

The base reason for this issue is the naming of the worksheets.

If the spreadsheet is automatically created / named, why can't the worksheet?
If manually created, then manually rename the worksheet.

In any case, can't spreadsheet worksheets be enumerated? E.g. referenced by position rather than name?
(I hate automating spreadsheet data - too loose for me, however I DO have to do it periodically only and thus am not (and have no intention of) becoming an expert with them).

ATB,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[newguy86]

Sorry for taking so long to respond, the holidays can be hectic!

To answer your questions, because the worksheet is a report that's generated by a 3rd party system, we don't have a way to change how/what the worksheet is named when it's generated.

Currently we are manually renaming the worksheet.

As far as referencing a worksheet by position rather than name, I thought the same thing but when I setup the linked table in Access it automatically linked it to the name of the worksheet which meant that whenever I uploaded a new report the link would fail since it now had a different name. If there's a way to setup my linked table to a filename and ignore the worksheet name (since it's the only worksheet in the file) then I'm all ears. Otherwise, I was thinking that it might be possible to programmically update the linked table whenever I opened the database.

Travis
Charter Media

 

[Darrylles]

newguy, ok, never one to reinvent the wheel:

http://stackoverflow.com/questions/1397158/connect-to-excel-with-ado-dont-know-sheet-names

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.