2

I am looking around at embedding Excel workbooks in Visio and doing read/write operations on the embedded data. Doing paste-special with Excel data seems to embed the whole workbook, not just the selected/copied data.

I'm able to get a reference to the Excel Workbook object embedded on the Visio page, but I'm not able to tell what sheet is active and being displayed in Visio. It seems that I can only tell what the active sheet is when the sheet is being edited. When it's just there on the page the Workbook object returns Nothing for the ActiveSheet and the workbook has no Windows. It makes sense it'd have no windows, but doesn't make so much sense that there is no active sheet.

Is there a way to find out what the 'active' sheet is in an Excel OLE object? Or should any code I write work under the assumption that everything is in sheet 1? I would probably be adding any embedded workbooks programmatically, so it wouldn't be too big a stretch to limit each book to one sheet, but I just want to make sure there's not some property I'm overlooking that will tell me what I want.

This code will find an Excel OLE object on the page and sets cells A1 and B1 in sheet 1 of the workbook:

Dim CheckShp As Visio.Shape
Dim CheckObj As OLEObject
For Each CheckObj In ActivePage.OLEObjects
    If CheckObj.ProgID Like "Excel.Sheet.*" Then
        Dim Sheet As Excel.Worksheet
        Dim Bk As Excel.Workbook
        Set Bk = CheckObj.Object
        Set Sheet = Bk.Worksheets(1)

        Sheet.Cells(1, 1) = "HELLO"
        Sheet.Cells(1, 2) = "WORLD"

    End If
Next CheckObj
S Meaden
  • 8,050
  • 3
  • 34
  • 65
Jon Fournier
  • 4,299
  • 3
  • 33
  • 43
  • could you get a reference to the workbook object without `Activating` or `Editing` the embedded OLE object anyway? – A.S.H Dec 27 '16 at 22:23
  • 2
    What code do you have so far? – BruceWayne Dec 27 '16 at 22:28
  • I can easily get a reference to the workbook, but I want to get a reference to the worksheet that's displayed when not editing the OLE object. There's nothing obvious for that, that I can see (ActiveSheet is Nothing for the workbook object unless the OLE object is being edited). – Jon Fournier Dec 28 '16 at 14:00

1 Answers1

-1

You don't need the ActiveSheet, which actually is nothing if you are not editing. You can iterate through Bk.Worksheets, from 1 to Worksheets.Count, to find one with a specific name.

Also, the index can be a name, so this should work:

Set Sheet = Bk.Worksheets("MySpecificSheet")
Carlos E. Ferro
  • 930
  • 10
  • 21
  • The question is 'what is the name of the sheet being displayed'. I know how to get a specific sheet. The question is asking how to find out what sheet is being displayed by the OLE object. – Jon Fournier Dec 29 '16 at 20:55
  • Sorry, I misunderstood the question. The answer to that is "There is no name, because there is no sheet being displayed, otherwise that would be the ActiveSheet" – Carlos E. Ferro Dec 31 '16 at 15:09
  • I understand that the Excel object is not actively rendering to the screen, and that OLE is showing me a graphic from the last time the sheet was rendered. I'm really looking to see if there's a property on the Workbook object or the OLE object that would tell me the name of the sheet that was rendered for display. – Jon Fournier Jan 04 '17 at 14:08