How to avoid unintentional link updates?

Posted by shane on May 01, 2001 12:40 PM

I have a 'source' spreadsheet(s.s.) with pivot tables on it. I have several 'desitination' s.s.'s that link to the 'source' s.s. Each 'desination' s.s. has a macro that will change the pivot table variables on the 'source' s.s. for the requirements of that particular 'destination' sheet. The problem arises when the 'source' s.s. is not open, but I open multiple 'destination' s.s.'s. What happens is that all the open 'destination' sheets that should not be updating their numbers (since the 'source' sheet is not open and I did not choose to update auto. links) automatically update their numbers to match those of the last 'destination' sheet that was opened. Is there any way to prevent this???

Posted by Dave Hawley on May 01, 2001 12:51 PM

Hi Shane

Not too sure I follow you, but you can prevent links updating with the Open method, like below:

Workbooks.Open FileName:="C:\MyDocuments\MyFile", UpdateLinks:=0

Dave

OzGrid Business Applications

Posted by Shane on May 01, 2001 1:26 PM

Thanks for the reply Dave. I have 2 followup questions. (1) how do I get a prompt box so that I can put in different file names (I have 7 different 'source' files and each one has 10 to 30 dependent 'destination files'
(2)is this the best way to do it if you have multiple users of the files who might not know how to use the code you suggested?

thanks!

Posted by Shane on May 01, 2001 1:27 PM

Thanks Dave - 2 followup questions

thanks!

Posted by Dave Hawley on May 01, 2001 1:43 PM

Shane, I wouldn't rely on users to type in file names into a InputBox, they WILL stuff it upe every time :o) Use the GetOpenFileName instead.

Sub TryThis()
Dim SfileToOpen As String
SfileToOpen = Application.GetOpenFilename
If SfileToOpen = False Then Exit Sub
Workbooks.Open FileName:=SfileToOpen, UpdateLinks:=0
End Sub

Dave

OzGrid Business Applications