Data linking to embedded Excel workbook, not an external workbook?

[sea-dubs]

Kit:
Visio 2010 Professional
Excel 2010

Short version: Is it possible to link to an embedded workbook?

I'm building a map of a piping system using Visio, which pulls in information about fluid flow from a 'companion' Excel workbook. When I am done, I will be sending copies of the Visio document to multiple people, and need to NOT have the Excel workbook in a central location. Each copy of the Visio drawing needs its own Excel workbook.

The easy solution to this is to embed the Excel workbook in the Visio document. I know how to do that--it's easy.

However, I can't figure out how to link to the Excel workbook if it is embedded. As far as I can see, the "link data to shapes" option only works for external sources. Is it possible to link to an embedded workbook?

(I have scrounged the internet for an answer, and haven't found anything. Someone else asked this question here: http://visguy.com/vgforum/index.php?topic=5451.msg21607#msg21607 , but they didn't get an answer.)

[Croc]

I work through the embeded OLE object.
Like this:

Code Select Expand

Dim sh As Visio.Shape
Sub ttt()
Set sh = ActivePage.Shapes("Sheet.1")
'MsgBox sh.ForeignType
'MsgBox ActivePage.OLEObjects(1).ClassID
'MsgBox ActivePage.OLEObjects(1).ProgID

Dim xlSheet As Excel.Worksheet

Set xlSheet = sh.Object.Worksheets(1)
xlSheet.Range("A1").Formula = 123
xlSheet.Range("A1").Offset(1, 1).Formula = "newText"
End Sub

[Nikolay]

Short version: Is it possible to link to an embedded workbook?

No, it's not possible.

[Croc]

The easy solution to this is to embed the Excel workbook in the Visio document.

After some thought...
"Is it not possible to link to an embedded workbook", but ...
Option 1: You can send the data directly to DataRecordSet. DataRecordSet stored in a file and sent along with the file. DataRecordSet displayed in the "External Data" window, and may be connected to the shapes.
Option 2: You can send the data in the field SolutionXML as XML and then import it through macro into a  DataRecordSet, using DataRecordSet.AddFromXML.
Option 3: You can write a macro to transfer data from the embedded worksheet into a DataRecordSet. This is the most difficult way, but the data (if necessary) can be updated. After update each time you need to run a macro for transfer them from embedded Excel into a DataRecordSet.