Geek Answers Handbook

Modify an Excel built-in workbook in a Word document through VBA

I have a Word document with two embedded Excel files (added using Insert -> Object -> Create From File) that I want to edit using Word VBA. I got to the point that I can open embedded files for editing (see the code below), but I can’t get a pen in an Excel workbook, using which I can make changes and save the embedded file. Does anyone have a solution for this? Thanks in advance.

Sub TestMacro()

    Dim lNumShapes As Long
    Dim lShapeCnt As Long
    Dim xlApp As Object
    Dim wrdActDoc As Document

    Set wrdActDoc = ActiveDocument

    For lShapeCnt = 1 To 1 'wrdActDoc.InlineShapes.Count
        If wrdActDoc.InlineShapes(lShapeCnt).Type = wdInlineShapeEmbeddedOLEObject Then
            If wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.ProgID = "Excel.Sheet.8" Then
                'This opens the embedded Excel workbook using Excel
                wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.Edit
            End If
        End If
    Next lShapeCnt

End Sub
+5
source share
3 answers

Yikes, , . , Excel ( , ).

-, Excel (Project- > References Microsoft Library Object Library). bona-fide Excel , "" . , , Intellisense .

, .OleFormat.Edit. ( .OleFormat.Activate, .Edit, , ).

.Activate(, ,.Edit), OleFormat.Object. Excel, "" Excel, :

Dim oOleFormat as OleFormat
Set oOleFormat = ...

oOleFormat.Activate

Dim oWorkbook As Excel.Workbook
Set oWorkbook = oOleFormat.Object

' Do stuff with the workbook
oWorkbook.Charts(1).ChartArea.Font.Bold = True

, Excel, - Word "" , , , . , , .

, . tell Word, - , . , , , , . , :

oOleFormat.ActivateAs "This.Class.Does.Not.Exist"

, , , On Error Resume Next. , . :

Private Sub DeactivateOleObject(ByRef oOleFormat as OleFormat)
    On Error Resume Next
    oOleFormat.ActivateAs "This.Class.Does.Not.Exist"
End Sub

, . Gary

+4

: find, - , .

..

With Selection.Find
    .ClearFormatting
    .Text = "wiffleball"
    .Execute Forward:=True
End With

, , .

, , .

+2

. -

Sub TestMacro()

    Dim lNumShapes As Long
    Dim lShapeCnt As Long
    Dim xlApp As Object
    Dim wrdActDoc As Document

    Set wrdActDoc = ActiveDocument

    For lShapeCnt = 1 To 1 'wrdActDoc.InlineShapes.Count
        If wrdActDoc.InlineShapes(lShapeCnt).Type = wdInlineShapeEmbeddedOLEObject Then
            If wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.ProgID = "Excel.Sheet.8" Then
                wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.Edit
                Set xlApp = GetObject(, "Excel.Application")
                xlApp.Workbooks(1).Worksheets(1).Range("A1") = "This is A modified"
                xlApp.Workbooks(1).Save
                xlApp.Workbooks(1).Close
                xlApp.Quit
            End If
        End If
    Next lShapeCnt

End Sub
+1

More articles:


All Articles