INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Automatically Update a Linked Table Filepath

Automatically Update a Linked Table Filepath

(OP)
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

RE: 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.

RE: Automatically Update a Linked Table Filepath

(OP)
Sorry for taking so long to respond, the holidays can be hectic! smile

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close