98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access Training and help » Linked tables - renaming the relevant worksheet | Access forum
Linked tables - renaming the relevant worksheet | Access forum
Resolved · Low Priority · Version 2007
Fri 31 Jan 2014, 17:05Delegate Sophia said...
Sophia has attended:
Excel Intermediate course
Access Advanced course
Linked tables - renaming the relevant worksheet
Hi,
I have a linked table in an Access database, let's call it Current Data File, which draws from an Excel file that is saved over with the latest data each week that our agency sends to us. The agency files are sent to us with a different name on the relevant worksheet each time, e.g. "31/01/14 Data". Once I save these files as Current Data File, I then have to rename the worksheet tab to match the original linked table.
The ageny have now agreed to send the data with a consistent worksheet name from week to week, but it is still different from what Access is expecting to read. Is there a way to tell Access once and for all that the worksheet has a new name? I know you can change the filepath with the Linked Table Manager, but I can't see a way to change the name of the worksheet within the Excel workbook.
Hope that makes sense! It sounds like a small thing, but it comes up a lot in similar situations, and it would be great to find a solution to the low-level frustration!
Sophia
Tue 4 Feb 2014, 09:31Trainer Simon said...
RE: Linked tables - renaming the relevant worksheet
Dear Sophia
I'm not sure I understand the question, but to if you wish to change the name of a worksheet in Excel, then double-click on the tab. This highlights the name, which can then be edited. Press <enter> or click elsewhere in the sheet when you are finished.
It's also a good idea to remove spaces from the sheetname (like a web address has no spaces). Thus "31/01/14 Data" might be written "31/01/14_Data.
Simon
Tue 4 Feb 2014, 09:42Delegate Sophia said...
RE: Linked tables - renaming the relevant worksheet
Hi Simon,
Thanks for the reply.
I know how to change the name of the tab in Excel - the problem is that I have to keep doing this, as the linked table in Access is expecting to find another tab name (the one that the original spreadsheet had when I first set up the linked table).
Rather than changing the name every time in Excel, I would rather tell Access to look out for the new tab name (which is consistent each time, but was originally something else). However, I can't see a way to do this.
Sophia
Tue 4 Feb 2014, 11:55Trainer Simon said...
RE: Linked tables - renaming the relevant worksheet
Go to Linked Table manager;
Select the link you wish to change;
Tick the "Always prompt for new locations" box;
Click <OK>
You should then be guided through the steps you are looking for.
Simon
Tue 4 Feb 2014, 11:56Trainer Simon said...
RE: Linked tables - renaming the relevant worksheet
Go to Linked Table manager;
Select the link you wish to change;
Tick the "Always prompt for new locations" box;
Click <OK>
You should then be guided through the steps you are looking for.
Simon
Tue 4 Feb 2014, 11:58Delegate Sophia said...
RE: Linked tables - renaming the relevant worksheet
Hi Simon,
Thanks for your speedy response! I have tried this, and although I can change the filepath from here, it doesn't solve the problem of the tab name. Unless there's something I'm missing...?
Kind regards,
Sophia
Fri 8 Aug 2014, 16:23Trainer Cindy said...
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Fri 15 Aug 2014: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Splittng a databaseSplit your database into two (at least). |
