Updating links
Posted by Grant on October 19, 2000 1:04 PM
What would you do in this scenario:
You have multiple workbooks that contain detail information. You also have a summary workbook that links back to the detail pages to pull values. The summary workbook is synchronized with a PDA device running Pocket Excel. This summary workbook is basically only used on the PDA and is rarely opened in Excel.
How do you get the linked information to update in the summary workbook when something is changed in one of the other workbooks without opening the summary in Excel and refreshing the links? The process should be automatic and transparent to the user, so that whenever the summary file is synchronized, it always has the most up-to-date information.
Any suggestions?
Posted by Ben O. on October 19, 2000 1:31 PM
This should work. Since it's named Auto_Close, it'll run whenever the user closes the workbook, whether he's made changes or not. I don't know how to make it so that it'll only run if he makes changes. Perhaps someone else does.
The screen updating is turned off so the user won't know that the summary workbook is being opened.
AsktoUpdateLinks is turned off so that dialog box won't open and ask the user if he wants to update the links or not. It just updates them automatically.
I tested the macro with two files on my desktop and it worked. Closing the file seemingly didn't not take any longer than usual. Of course, both the master and the source are on my desktop. If the files are on a network it might go slower. And it will also be slower if there are a lot of linked cells. I only had one.
Sub Auto_Close()
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False
Dim Original As String
Original = ActiveWorkbook.Name
Workbooks.Open Filename:="C:\WINDOWS\Desktop\Book1.xls"
ActiveWorkbook.Save
ActiveWindow.Close
Windows(Original).Activate
Application.AskToUpdateLinks = True
ActiveWorkbook.Close
End Sub
-Ben
