Automatically Update a Linked Table Filepath

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.

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

newguy, ok, never one to reinvent the wheel:


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