causing embedded spreadsheets to calculate from VBA in Visio

[NewPSC]

Hello! I've heard there are some very sharp individuals participating in this
forum, so this should be easy!

Long story, but punch line is I can't seem to get the Calculate method to,
um, calculate. We have a Visio diagram that has an embedded spreadsheet.
Through a Visio macro, we populate Sheet2 of that spreadsheet, and want
subsequently to cause Sheet1 to calculate.

I can get to Sheet1 and access or change Cells(x,y), and VB does not
complain about using ".Calculate", but so far the only way to actually update
is to manually open the embedded sheet and hit PF9. I've also noted that if
I manually open that worksheet and run a macro in the worksheet itself that
has a .Calculate call, that works too.

Thanks!

The code in question follows:

Dim xGridShape As shape

CurrentDoc = ActiveDocument.Name

For j = 1 To Documents(CurrentDoc).Pages.Count

For k = 1 To Documents(CurrentDoc).Pages(j).Shapes.Count

With Documents(CurrentDoc).Pages(j).Shapes(k)

shapename = Documents(CurrentDoc).Pages(j).Shapes(k).Name
If InStr(shapename, "SCOT_Grid") Then
' Set xws = Documents(CurrentDoc).Pages(j).Shapes(k).Object
Debug.Print "recalcing"; shapename

Documents(CurrentDoc).Pages(j).Shapes(k).Object.Worksheets(1).Calculate

End If
End With
Next k
Next j

 

[Andy]

I found trying to do anything with the embedded worksheet within visio
to be very limited, without opening it.

My suggestion would be to hold your data within a real excel
spreadsheet and use automation from Visio to access it. In my case I
was using Excel already as a database, then writing a value into a
worksheet that then did a calculation. I then picked up the calculate
value for use in my visio application.