Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links in formulas change when another user runs a workbook - 2003
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 --- Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 11/24/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links in formulas change when another user runs a workbook - 2003
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" wrote in message ... 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 --- Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 11/24/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links in formulas change when another user runs a workbook - 2003
Sharad -- Thanks. I think we will add code to open the workbooks. Larry "Sharad Naik" wrote in message ... 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" wrote in message ... 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 --- Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 11/24/2004 --- Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 11/25/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Links in formulas change when another user runs a workbook - 2003
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 "L Mehl" wrote in message ... Sharad -- Thanks. I think we will add code to open the workbooks. Larry "Sharad Naik" wrote in message ... 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" wrote in message ... 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 --- Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 11/24/2004 --- Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.801 / Virus Database: 544 - Release Date: 11/25/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompt user on automatic updates for Workbook Links | Excel Discussion (Misc queries) | |||
remove links when pasteing formulas from one workbook to anoth | Excel Worksheet Functions | |||
Open Workbook created in Excel 2003 in a computer that runs Excel | Excel Discussion (Misc queries) | |||
prompt user to change source of links | Excel Discussion (Misc queries) | |||
Links in formulas change when another user runs a workbook | Excel Discussion (Misc queries) |