Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Calculations
I'm using a Workbook that has macros in it for updating Charts when input
and calculations change. I do not have access to the code for the Workbook. I have multiple instances with different filenames of this Workbook open in tile mode, with links in both directions to another "Summary" Workbook. The instances of the Workbook with the Chart macros won't update until I click individually on them. So I need to click on each one of them to update the "Summary" Workbook properly. Is there a procedure I can use to recalculate all of the instances of the Workbooks with the Chart macros at the same time? Thanks, Fred |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Calculations
Possibly
Application.CalculateFullRebuild or Application.CalculateFull Your a little vauge about what triggers your macros. If it is a workbook_activate event then You might need to do for each bk in Workbooks if lcase(bk.Name) < "summary.xls" then bk.Activate Activesheet.Range("A1").Select end if next workbooks("Summary.xls").Select -- regards, Tom Ogilvy "Fred Ernst" wrote: I'm using a Workbook that has macros in it for updating Charts when input and calculations change. I do not have access to the code for the Workbook. I have multiple instances with different filenames of this Workbook open in tile mode, with links in both directions to another "Summary" Workbook. The instances of the Workbook with the Chart macros won't update until I click individually on them. So I need to click on each one of them to update the "Summary" Workbook properly. Is there a procedure I can use to recalculate all of the instances of the Workbooks with the Chart macros at the same time? Thanks, Fred |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Calculations
Thank you Mr. Ogilvy. I will give these a shot.
From what I can tell the Workbook calculates and updates the Charts when: 1) a cell that is used by a formula in the Workbook is changed manually 2) Activate a Sheet in the Workbook by clicking on and/or changing Sheets in the Workbook 3) Clicking on the Workbook when in tile mode with other Workbooks open. #3 could just be another Sheet Activate event I guess. Ideally, I would like to change the inputs to the Chart Workbooks by using links to the "Summary" Workbook and see the results of certain source cells in the Chart Workbooks in the destination "Summary" Workbook. I tried this but the Chart Workbooks were not updating even though the input cell on the Chart Workbook that was linked to the "Summary" Workbook changed after changing the source cell in the "Summary" Workbook.. I'm really just using the Chart Workbooks for calculation engines and wanting to use the "Summary" workbook to change input to the Chart Workbooks and see the results. Lastly, it really would be nice if I didn't have to have all of these Chart Workbooks open. Is there anyway to use them as calculation engines in the same directory, but Closed? Thanks, Fred "Tom Ogilvy" wrote in message ... Possibly Application.CalculateFullRebuild or Application.CalculateFull Your a little vauge about what triggers your macros. If it is a workbook_activate event then You might need to do for each bk in Workbooks if lcase(bk.Name) < "summary.xls" then bk.Activate Activesheet.Range("A1").Select end if next workbooks("Summary.xls").Select -- regards, Tom Ogilvy "Fred Ernst" wrote: I'm using a Workbook that has macros in it for updating Charts when input and calculations change. I do not have access to the code for the Workbook. I have multiple instances with different filenames of this Workbook open in tile mode, with links in both directions to another "Summary" Workbook. The instances of the Workbook with the Chart macros won't update until I click individually on them. So I need to click on each one of them to update the "Summary" Workbook properly. Is there a procedure I can use to recalculate all of the instances of the Workbooks with the Chart macros at the same time? Thanks, Fred |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Calculations
Not if you want them to calculate. A closed file in a directory is nothing
but a collection of binary bits. The excel application is what turns them into usable information and performs the calculations. If you are just pulling data from them (the last time they calculated is sufficient), then yes, you can link to a closed workbook (just close the chart workbook(s) - the linking formulas will adjust). However, if your problem is that things aren't updating, then this will probably just make the problem worse. ----------- If calculation is what causes the updating you need, then try what I offered. -- Regards, Tom Ogilvy "Fred Ernst" wrote in message ... Thank you Mr. Ogilvy. I will give these a shot. From what I can tell the Workbook calculates and updates the Charts when: 1) a cell that is used by a formula in the Workbook is changed manually 2) Activate a Sheet in the Workbook by clicking on and/or changing Sheets in the Workbook 3) Clicking on the Workbook when in tile mode with other Workbooks open. #3 could just be another Sheet Activate event I guess. Ideally, I would like to change the inputs to the Chart Workbooks by using links to the "Summary" Workbook and see the results of certain source cells in the Chart Workbooks in the destination "Summary" Workbook. I tried this but the Chart Workbooks were not updating even though the input cell on the Chart Workbook that was linked to the "Summary" Workbook changed after changing the source cell in the "Summary" Workbook.. I'm really just using the Chart Workbooks for calculation engines and wanting to use the "Summary" workbook to change input to the Chart Workbooks and see the results. Lastly, it really would be nice if I didn't have to have all of these Chart Workbooks open. Is there anyway to use them as calculation engines in the same directory, but Closed? Thanks, Fred "Tom Ogilvy" wrote in message ... Possibly Application.CalculateFullRebuild or Application.CalculateFull Your a little vauge about what triggers your macros. If it is a workbook_activate event then You might need to do for each bk in Workbooks if lcase(bk.Name) < "summary.xls" then bk.Activate Activesheet.Range("A1").Select end if next workbooks("Summary.xls").Select -- regards, Tom Ogilvy "Fred Ernst" wrote: I'm using a Workbook that has macros in it for updating Charts when input and calculations change. I do not have access to the code for the Workbook. I have multiple instances with different filenames of this Workbook open in tile mode, with links in both directions to another "Summary" Workbook. The instances of the Workbook with the Chart macros won't update until I click individually on them. So I need to click on each one of them to update the "Summary" Workbook properly. Is there a procedure I can use to recalculate all of the instances of the Workbooks with the Chart macros at the same time? Thanks, Fred |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Calculations
The calculation updates did not work for me.
I turned on the macro recorder and repeated the action that causes the Chart Workbook to run and for my my "Summary" workbook to hence update, and this is what it recorded. Now I'm just trying to get it to loop through all the open workbooks and run that macro !OnGradeMain. However I'm finding it a callenge to get the filename and macro name into the Application.Run function properly From the Macro Recorder: Windows("d10-Street Capacity-35th-Street-Vertical Curb-UD-Inlet_v2.14a.xls"). _ Activate Application.Run _ "'d10-Street Capacity-35th-Street-Vertical Curb-UD-Inlet_v2.14a.xls'!OnGradeMain" Windows("Hydrology Summary.xls").Activate My Attempt so far, no luck yet: Sub FullUpdate() Dim bk As Workbook For Each bk In Workbooks If LCase(bk.Name) < "hydrology summary.xls" Then Windows(bk.Name).Activate Application.Run "" '" & bk.name & "'" & " !OnGradeMain" End If Next Windows("Hydrology Summary.xls").Activate End Sub Thanks, Fred |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Calculations
The command should be:
Application.Run "'" & bk.name & "'!OnGradeMain" Well, the calculation updates wouldn't work. I only suggested them based on your description. You never mentioned that in each workbook, after clicking it, you went to Tools=Macro=Macros, selected the OnGradeMain macro for the current workbook and hit the run button. The instances of the Workbook with the Chart macros won't update until I click individually on them. So I need to click on each one of them to update the "Summary" Workbook properly. See mentioned anywhere of manually running macros? -- Regards, Tom Ogilvy "Fred Ernst" wrote in message ... The calculation updates did not work for me. I turned on the macro recorder and repeated the action that causes the Chart Workbook to run and for my my "Summary" workbook to hence update, and this is what it recorded. Now I'm just trying to get it to loop through all the open workbooks and run that macro !OnGradeMain. However I'm finding it a callenge to get the filename and macro name into the Application.Run function properly From the Macro Recorder: Windows("d10-Street Capacity-35th-Street-Vertical Curb-UD-Inlet_v2.14a.xls"). _ Activate Application.Run _ "'d10-Street Capacity-35th-Street-Vertical Curb-UD-Inlet_v2.14a.xls'!OnGradeMain" Windows("Hydrology Summary.xls").Activate My Attempt so far, no luck yet: Sub FullUpdate() Dim bk As Workbook For Each bk In Workbooks If LCase(bk.Name) < "hydrology summary.xls" Then Windows(bk.Name).Activate Application.Run "" '" & bk.name & "'" & " !OnGradeMain" End If Next Windows("Hydrology Summary.xls").Activate End Sub Thanks, Fred |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Calculations
Thank you, that function works great now. Now I have confidence that the
calculations are firing and the Summary sheet is getting updated properly. Yeah, I never initiated the macros from the Tools menu. I just turned on the Macro Recorder and clicked on the calculation sheet. It actually took two clicks. One to activate the window I guess and then one to activate the sheet I presume or a click-event, and that macro turned up. The order the calculation workbooks calculate matters. I found as long as I initially open them in the order I want them to calculate, the loop will work in that order. Thanks, Fred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculations from tab to tab not automatically updating | Excel Worksheet Functions | |||
Why not automatically updating calculations? | Excel Discussion (Misc queries) | |||
Updating calculations automatically when i enter in new data | Excel Discussion (Misc queries) | |||
Updating calculations + graph when i enter in new data WITHOUT OFF | Excel Worksheet Functions | |||
updating links with calculations in many workbooks | Excel Programming |