Archive of Mr Excel Message Board


Back to Pivot Tables in Excel archive index
Back to archive home

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???

Re: How to avoid unintentional link updates?

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


Re: How to avoid unintentional link updates?

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!


Thanks Dave - 2 followup questions

Posted by Shane on May 01, 2001 1:27 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!


Re: How to avoid unintentional link updates?

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.