change linked sheet name

[webeinky]

Hi,

I have Access programs that are linked to Excel spreadsheets. Due to
circumstances beyond my control the sheet names have been changed and I will
have to use the new names in my programs.

I have tried to go into Link Manager but it does not let me change the sheet
name only the file name.

Does anyone know an easy way to change the Sheet name in a linked Excel
spreadsheet without messing up the connections in queries etc.

Thank you in advance for your help

 

[golfinray]

When you go to the linked table manager, click the button down at the bottom
that say "always check for new location." Then give it the new name and file
location.

 

[webeinky]

Golfinray,

Thank you for the speedy reply. I have clicked on the "always check for new
location".

It is the SHEET name I am trying to change (same location and file name). I
did try to include the sheet name such as: e:\Folder\filename.xls\'Sheet1$'
but it says it is not accessible.

 

[golfinray]

All you changed was the name of the sheet? Are there multiple sheets? If so,
try importing (file/get external data) to bring the sheet into the database.
Then if necesasary copy that sheet back to its original loaction and try
linking again. If there are multiple sheets on each it may not know which
sheet to bring in.

 

[webeinky]

Yes, all that has changed is the name of the sheet. (this all has been
caused because the software program that we use to export the data from into
an Excel spreadsheet used to name the tab in Excel the same as the Excel file
name..now it is Sheet1. We just did a software update, that is why there has
been a change)

Before the update there was only one tab created in Excel (named the same as
the file name). Now there are three tabs - Sheet1, Sheet2 Sheet3 - with
Sheet2 and Sheet3 are blank.

Right now my Access Program in Linked Table Mananger shows
e:\Folder\filename.xls\filename$
as the link path and I want to change it to
e:\Folder\filename.xls\Sheet1$


Thanks,

Nancy

 

[david]

name..now it is Sheet1. We just did a software update, that is why there

has been a change)

Because Excel changed. (A very long time ago) The old software
created an old type of Excel spreadsheet: the new software creates
a spreadsheet using a newer Excel format.

And when I was working on stuff like this, we had to support all
variations for import or export, so we had to support both named
sheets and "Sheet1".

And I thought it was pretty skanky.

But don't blame your supplier: blame MS.


Anyway. CHECK FIRST.

IMPORT the new sheet as a link, and CHECK the new link
to see what the correct link looks like.

If the linked table manager won't work correctly, it is possible
to use code (there is sample code) to modify the links, but
in your case it would probably more work than just re-creating
and re-naming the links.

As a matter of practice, I very seldom used linked tables
for spreadsheets: I more often used linked queries. Using
a linked query, you can change to SQL view and modify
the connection string by hand.

(david)