Adressing an imbeded Excel-Sheet via VBA

Started by felsioufy, August 30, 2021, 05:23:19 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

felsioufy

Dear all,
I know how to open and Process Excel-Files via VBA in Visio.
I also know how to create an excel Spreadsheet as a Visio-Shape in Visio.
Now I would like to combine both: means
access the imbeded Excel Sheet (in other words: read an write into the cells) via VBA.
Can anybody help me to find out how to adress the imbeded Data as if it was an external file?

Thank you in advance for your help.
Felsioufy

Surrogate

#1
Why you cant read data direct from Excel workbook (not embed) ?

Surrogate

#2
Sub felsioufy()
Dim sh As Shape ' some shape
Dim ew As Object ' embedded workbook
Dim es As Object ' sheet in embedded workbook
Set sh = ActivePage.Shapes.ItemFromID(6) ' define some shape
If InStr(sh.ProgID, "Excel") Then ' check is this shape excel object
Set ew = sh.Object ' define embedded workbook
Set es = ew.Worksheets(1) ' define sheet
MsgBox es.Cells(4, 1) ' msgbox with value from 4 row, 1 column of sheet
End If
End Sub
This my simple example. Without analyzing the number of sheets and ranges in them!