Consolidate Viso embedded worksheet objects into Excel or Access

[DML]

I have several Visio drawing files that contain an embedded worksheet object.

Using VBA, I would like to loop through each file, and copy the contents of
the embedded worksheet object into a consolidated Excel worksheet or Access
database.

Thanks for any recommendations on how to accomplish this.

David

 

[Barb Way]

Interesting idea! There is an older article and demo on MSDN that provides
a nice intro to handling embedded Excel objects. You can review it at
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarvisio/h
tml/candy_equipment.asp, and download the self-extracting zip here
http://download.microsoft.com/download/visio5/utility/1.0/win98mexp/en-us/Ca
ndyEquipment.exe.

Basically the key is that the embedded Object links you to the Excel Object
Model. Here's a brief sample of what you can do to get started...

Note: To use the following code, set a Reference in the VB editor to the
Excel type library.
Alternatively, dimension the xlSheet object as Object, instead of
Excel.Worksheet.
'******************************************************************
Dim xlSheet As Excel.Worksheet '
The inserted Excel object
Dim Shp as Visio.Shape
' Need a shape reference for the inserted object
Set Shp = Application.ActiveWindow.Page.InsertObject("excel.sheet",
visInsertAsEmbed)

' Once inserted, the worksheet is a Visio shape:
Set xlSheet = Shp.Object.Worksheets(1) ' The
property to address the shape's contents is the Object property.
xlSheet.Cells(1, 1).Value = "Column 1" '
cell reference and value
xlSheet.Range("B1").Formula = "=20+5" ' range
reference and formula

' ******************************************************************

You should definitely download the Candy Equipment demo, it's nicely done
and very helpful!


Barb Way
Product Support - Visio
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.