VBA Edit Embedded Excel Worksheet

[wapperdude]

A recent post, https://visguy.com/vgforum/index.php?topic=10548.msg49554;topicseen#msg49554, sent me on a bunny trail.  The post was focused on a syntax error successfully answered by Nikolay.  But, it made me wander if a similar topic existed on the Forum covering the topic more fully.  Searching yielded many Excel Viso related issues, but not this specific issue of how to use VBA to open and edit an embedded Excel worksheet.  My apologies if I missed such a topic.

Turns out to be fairly straight forward.  I hate dealing with OLE objects, and code syntax is difficult...like pulling teeth.  The approach starts with single click selection of the worksheet, and then run the code.  No error checking.  The code opens worksheet in new Excel window.  Manual editing may be performed, or the code could contain automated steps.  There is a placeholder for such.  After all is completed, code asks to save or not.

Code:

Code Select Expand

Sub editXcel()
   
    Dim xlApp   As Object
    Dim xlWb    As Object
    Dim xlWs    As Object
    Dim OLEObjects As Visio.OLEObjects
   
    Set OLEObjects = ActivePage.OLEObjects  'declare OLE objects on page
   
    Set xlApp = OLEObjects(1).Application   'declare Excel app
    Set xlWb = OLEObjects(1).Object         'declare Excel workbook
   
    xlWb.Activate
    xlWb.Application.Visible = True
    xlWb.Windows(1).Visible = True
    xlApp.Visible = True
   
    Set xlWs = xlWb.Worksheets(1)           'set the Worksheet that was selected of drawing page
    Call configWS(xlWs)                     'send the worksheet to macro for configuring / editing
   
EndIt:
    If MsgBox("Save Excel file changes?", vbYesNo, "Excel Update") = vbYes Then
        xlWb.Close SaveChanges:=True
    Else
        xlWb.Close SaveChanges:=False
    End If
    xlApp.Quit
   
End Sub
Sub configWS(xlWsh As Excel.Worksheet)
    Debug.Print xlWsh.Name
     
End Sub


[wapperdude]

Did minor update to check if selection is made before running macro.  Added file save backups for the original and modified worksheets. Added example code to format the worksheet.  Plus, removed undesired and unnecessary quit statement at the end of the file.

Code Select Expand

Sub editXcel()
   
    Dim xlApp   As Object
    Dim xlWb    As Object
    Dim xlWs    As Object
    Dim OLEObjects As Visio.OLEObjects
   
    If ActiveWindow.Selection.Count = 0 Then
        MsgBox " 1st select embedded worksheet."
        Exit Sub
    End If
   
    Set OLEObjects = ActivePage.OLEObjects  'declare OLE objects on page
   
    Set xlApp = OLEObjects(1).Application   'declare Excel app
    Set xlWb = OLEObjects(1).Object         'declare Excel workbook
   
    xlWb.Activate

    xlWb.Application.Visible = True
    xlWb.Windows(1).Visible = True
   
    Set xlWsCpy = xlWb.Worksheets(1)        'set the Worksheet that was selected of drawing page
    Set xlWs = xlWb.Worksheets(1)           'set the Worksheet that was selected of drawing page
    xlWb.SaveAs ("xlWBbkup.xlsx")           'Safety backup
    Call configWS(xlWs)                     'send the worksheet to macro for configuring / editing
    xlApp.Visible = True
    xlWb.SaveAs ("WAP xlWBmod.xlsx")         'create modified backup file

EndIt:
    xlApp.Quit                              'Save, Don't, Cancel
   
End Sub
Sub configWS(xlWsh As Excel.Worksheet)
    Dim LastCol As Long                                  'These are only variables used in this sub.
    Dim LastRow As Long
    Dim p As Integer
    Dim q As Long
    Dim rowCell As Range
    Dim FirstRow As Range
    Dim myUsedRng As Range
   
    Set xlWs = xlWsh
    Set myUsedRng = xlWs.UsedRange
    Set FirstRow = myUsedRng.Rows(1).Cells              'This syntax seems to work!!!
       
    'Row and column index counters
        LastCol = myUsedRng.Columns.Count               'This is last populated column.
        LastRow = myUsedRng.Rows.Count                  'This is last populated row.
       
        With myUsedRng
         'Cell text alignment (center, middle)
            .HorizontalAlignment = xlGeneral
            .VerticalAlignment = xlCenter
       
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
       
         'Add light borders around all individual used cells
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeBottom).Weight = xlThin
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlInsideVertical).Weight = xlThin
            .Borders(xlInsideHorizontal).Weight = xlThin
           
         'Heavy outer border around the used worksheet region
            .Borders(xlEdgeLeft).Weight = xlMedium
            .Borders(xlEdgeTop).Weight = xlMedium
            .Borders(xlEdgeBottom).Weight = xlMedium
            .Borders(xlEdgeRight).Weight = xlMedium
           
         'Set used cell background fill to white
            .Interior.Color = RGB(255, 255, 255)
        End With
       
     'Use First row to set column widths
        For Each rowCell In FirstRow
            rowCell.EntireColumn.AutoFit
        Next rowCell     
End Sub