How to get ActiveSheet in OLE-embedded Excel object

Question

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

Answer 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")