Documentation for Embedded Excel Object model... where is it?

[grtyvr]

Given all of the questions that float out in the googleshpere about how to access embedded Office Objects using VBA, I am led to believe that no documentation exists.  Is that true?  I try to use the macro recorder while I have the embedded Excel object open, but the methods it uses do not seem to be available from Visio VBA.

Try this:

embed an excel spreadsheet in a blank document.

private sub CopySheet()

Dim xlSheet as Excel.Worksheet
Dim xlWorkbook as Excel.Workbook
Dim xlWorksheets as Excel.Worksheets
Dim shpWorkbook as Visio.Shape

'one would expect this to work
set xlWorkbook = shpWorkbook.Object.Workbook(1)

'And finaly
xlWorkboo.Sheets("Sheet 1").Select
xlWorkbook.Sheets("Sheet 1").copy before:=xlWorkbook.Sheets(1)

'but no.....

end sub

[Jumpy]

I have no clue to this, but it would interest´me, too. Are you talking about embedded objects via OLE?
Has the OLE object to be referenced first?

[AndyW]

Have you checked the Microsoft Excel x.xx Object Library in the VBA project references?

Visio 2003 VBA IDE, Tools in menu bar, then References...

If not the Excel object model will not be available.

[grtyvr]

Yes.  I have added references to Excel.  The "Intelisense" does not give much help.  It does not show a list of methods and properties.

[aledlund]

as a note from the side, when I want to work with Excel using vba I do the development with Excel and then copy the code. (after all that is the object model you're attempting to master).
al

[Jumpy]

I think the problem is, to get access to an embedded Excel-File. If it is a separate Excel-File or a linked Excel-File, than the original file exists and can be accessed through its filename, that is:
- Referecne Excel-Library in VBE
- Open an Ecxel-Application
- Open the file
- ...

But when the file is embedded, you can still change the original file, but because it's not linked, the embedded file in the Visio document, will not change/update. So it would be nice, to get a grip on that embedded file.

Yesterday evening I experimented a bit with a Visio document, that had only one embedded Excel-File.
That file seems to be embedded with OLE in a shape.

That shape had an Object Method or Property, that gives you an IDispatch interface - whatever that is - according to offline help.
I couldn't find out, how to use that interface.

The page on the other hand, had an OLE-Objects collection which gave me access to the program ID of the embedded object with:

ActivePage.OLEObjects(1).ProgID

That was sth. like Excel.Sheet.12 and could help to know the kind of Application needed (in this case excel).


But how to bring that all together to get a grip on the embedded file? I didn't manage. And "googeling" IDispatch led to MSDN and SDK sites that were way over my head, so I understood nothing. So if anyone knows how to do that, it would be nice to know.

Maybe I was thinking to complicated anyway and there is an easier way to do this?

Jumpy