CreativeResources
asked on
Completely baffled by Excel 2003 worksheet links that cannot be updated.
The set-up:
I use Office 2003.
20 different Excel spreadsheets, to which I will refer as “source files.”
1 Manager’s Summary file, containing links to the source files so that each of the 20 source files feeds information into one of 20 different rows in the summary file.
The problem:
When I open the summary file I get a screen that says, “This workbook contains links to other data sources."
When I click "Update" I get a second screen that says, “This workbook contains one or more links that cannot be updated."
When I click on "Edit Links" all linked files are listed as “OK.”
The data is actually updated correctly, so the summary file is correctly reading all the source files.
I just want to get rid of the second error message mostly because it is a nuisance, but also because it causes the managers reading the summary to question the accuracy of the summary file.
Anticipating your questions:
The source files contain no named ranges at all.
The source files contain no macros at all.
None of the source files are in HTML.
Your help will be greatly appreciated!
I use Office 2003.
20 different Excel spreadsheets, to which I will refer as “source files.”
1 Manager’s Summary file, containing links to the source files so that each of the 20 source files feeds information into one of 20 different rows in the summary file.
The problem:
When I open the summary file I get a screen that says, “This workbook contains links to other data sources."
When I click "Update" I get a second screen that says, “This workbook contains one or more links that cannot be updated."
When I click on "Edit Links" all linked files are listed as “OK.”
The data is actually updated correctly, so the summary file is correctly reading all the source files.
I just want to get rid of the second error message mostly because it is a nuisance, but also because it causes the managers reading the summary to question the accuracy of the summary file.
Anticipating your questions:
The source files contain no named ranges at all.
The source files contain no macros at all.
None of the source files are in HTML.
Your help will be greatly appreciated!
Oops sorry I did not fully read your post, please ignore the above comment
Michael
Michael
If nothing else you could just disable automatic link updating.
Goto Edit>Links...
Click on Startup Prompts...
Select the option, "Don't display the alert and don't update automatic
links."
Michael
Goto Edit>Links...
Click on Startup Prompts...
Select the option, "Don't display the alert and don't update automatic
links."
Michael
ASKER
Michael, Thanks for taking the time to respond. Let's assume that I disable the automatic link updating. What is the easiest way for the managers to update the links manually when they open the summary files? Or, I suppose I could also just write a quick updating macro that they can launch with an "Update" button. Does that make sense? Thanks again.
A macro to update links would be the easiest way for novice users to update the links. Just link the attached macro to a button.
Michael
Michael
Sub UpdateLinks()
Application.DisplayAlerts = False
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
Application.DisplayAlerts = True
End SubASKER
Hey, Michael, thanks again for staying on this. Can the macro you have suggested be set up so that it runs automatically when the file is opened or must it be connected to a button? Thanks again!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Michael, Thanks for taking the time to help me with this problem. The solution actually helped me with a copule of other issues I was having.
http://support.microsoft.com/kb/813977
Michael