Links in formulas change when another user runs a workbook - 2003

[L Mehl]

Hello --

When another user runs a set of workbooks I built for him, links in formulas
include the name of the folder in which he stored the set of xls files.

In my original files, links simply look like
workbook.xls!range_name

His workbooks don't work. and, when he sends them to us to fix, the links
have to be changed.

MS article PSS ID Number: 327006 seems to be relevant to this problem.
If I understand the article, it suggests:

"To temporarily prevent the recalculation of external links, set the
calculation environment to Manual... "

I can see how to do that in code placed in Workbook_Open,
but I can't figure out where to turn recalculation back ON.

Am I on the right track with this as a solution to the problem?

If so, can someone tell me where to put code to turn recalculation ON.

If not, do you have an idea of how the links become changed?

Thank you for any help.

Larry Mehl

 

[Sharad Naik]

Hi

Say workbok 'one.xls' ans links to 'two.xls' then
When you open 'one.xls', if 'two.xls' is also open on same machine you will
see the links without the file path
of two.xls. If two.xls is not open you will see the link with
full file path of two.xls.

Two switch back the calculations to auto you can put code like below
in ThisWorkBook SheetChange Event procedure
(This is assuming the links are in a single workbook named "two.xls")

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For Each w In Application.Workbooks
If w.Name = "two.xls" Then
Application.Calculation = xlCalculationAutomatic
Exit For
End If
Next w

End Sub


OR
As I do it for some templates, in the workbook_open procedure,
instead of turning off auto calculations and then setting it to auto
again as above, you can simply add code to open the linked workbooks, and
hide them if you like.

Sharad

 

[L Mehl]

Sharad --

Thanks. I think we will add code to open the workbooks.

Larry

 

[Sharad Naik]

Hi Mehl,
Further when you add the code to open the workbook,
If the workbook is already open, it not only generates error, but displays a
seperate warning message to the user, even if you do On Error Resume Next.
Therefore put the code to check if the workbook is already open before the
code for opening it.
e.g., for opening one.xls :

If Workbooks("one.xls").Parent Is Nothing Then
Workbooks.Open FileName:="C:\one.xls"
ThisWorkbook.Activate
Application.Windows("one.xls").Visible = False
End If

Sharad