How to update master workbook with links to other workbooks

I have a workbook that links out to numerous other workbooks for current data. After one of the supporting workbooks gets up dated, I return to the master workbook. Here, I need to see the updated results. The only way that I can find a way of updating the master is to close and save it and then re-open.

Is there a way of updating the master workbook using a macro?

The updating should be instantaneous. If you go to Tools -> Options under the calculation tab, is the calculation set to automatic? You can recalc with F9. Or, if that doesn't work, go to Edit -> Edit Links. You can update the values manually on the right with Update Values, and make sure near the bottom the Update: is set to Automatic.

Thanks Dave
I tried you suggestions and neither worked. All the setting are correct.

That's why I posted this in programming.

Still open for suggesstions and ideas.

Still need help please.

Hi Launchnet,

See if the following is what you need - just change the paths and file names to suit:

Please Login or Register  to view this content.

HTH

Robert

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

This applies to using unnecessary wording like "How to" which make searches less effective. I will remove them this time only!

Hi Trebor76 . . .
Your input gave me the lead to solving the updating of links.

Here is the code that I used.

Please Login or Register  to view this content.

With a button on the workbook I can update at anytime.

Now that I look at this deeper, is there a way that I can update the master workbook when I re-display the master workbook. In my case, I have a number of workbooks open, including the Master, and after I have updated and saved the changes to any linked workbook, I display the Master workbook.

It would be like using the "On Open" routine. I can't find a routine for when the master workbook is re-activated.

hi Matt

Originally Posted by Launchnet

...It would be like using the "On Open" routine. I can't find a routine for when the master workbook is re-activated.

Open the VBE,
double click on the Thisworkbook module,
then at the top of the Code window, change the left dropdown to "Workbook",
look at all the options on the right dropdown for the possible events (eg "Activate").
After choosing one of the events, an "empty" macro will be created in the code window.

The [F9] or [ctrl + shift + alt + F9] should force a recalculation, but as someone mentioned this should be happening automatically. Does the Status bar (bottom left of the screen) say "Calculate"?

It's been a while since I've spotted your threads & I've forgotten which version of Excel you use. Are you able to update your profile to state which version of Excel you are using?

hth
Rob

Hi Broro183 . . .

Excel Version is 2002 SP3
It does not show calculate on the status bar.

I want to point out, that I am looking for this to happen automatically if at all possible. It is going to be used on many different computers, so I don't want to change anything that would not function on another users computer or require them to change settings.

Currently the button I have added to the Master workbook works fine. It just would be nice if it would function automatically.

Hi Launchnet,

Try turning off the "Do You Want to Update External Links" prompt on the Master workbook so it just does it by default via the following four steps:

1. From the Tools menu select Options
2. If it isn't already selected, click (select) the Edit tab from the Options dialog
3. Untick the Ask to update automatic links check box
4. Click OK

HTH

Robert

Trebor76 . . .

I just checked and it is already unchecked.

The only other thing I can suggest then is to run the Master workbook code on a timer basis, say every minute. Depending on the number of tabs and links though this may not be ideal.

Thanks for all the efforts and head scratching . . . I think all of the scratching that I do is why I am bald.

So far, the button is still much better, because it is very easy.

Automated when the screen becomes the top most screen would be the best, but I know that's far beyond me.

Hi Chris,

Welcome to the Forum.

It will be good if you start a new thread with your issue. You are violating one of the forum rules which states that you cannot post your thread in the thread of another. Besides, this thread is almost 2 yrs old.

Start a new thread and you will get help much faster. You can provide the link to this thread in your post, so that people can relate to it and reply to you.