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

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

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

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

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

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

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