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!
CreativeResourcesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:
Are the links referencing a workbook saved in HTML format? If so

Excel cannot update links from a workbook that is saved in HTML file format if that workbook is closed. Excel updates the links correctly only if the link source file is already opened.

http://support.microsoft.com/kb/813977

Michael
0
Michael FowlerSolutions ConsultantCommented:
Oops sorry I did not fully read your post, please ignore the above comment

Michael
0
Michael FowlerSolutions ConsultantCommented:
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
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

CreativeResourcesAuthor Commented:
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.
0
Michael FowlerSolutions ConsultantCommented:
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


Sub UpdateLinks()
   Application.DisplayAlerts = False
   ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
   Application.DisplayAlerts = True
End Sub

Open in new window

0
CreativeResourcesAuthor Commented:
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!
0
Michael FowlerSolutions ConsultantCommented:
To run a VBA script on start up you use the WorkBook_Open event.

I have attached an example file for you to see it in action. Just Open the workbook, press ALT+F11 to open VBA editor and double click on ThisWorkbook to see the function

Note - This will only work if macros have been enabled

Michael
Private Sub Workbook_Open()
   Application.DisplayAlerts = False
   ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
   Application.DisplayAlerts = True
End Sub

Open in new window

example-file.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CreativeResourcesAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.