VBA Word: Do While Loop

Question

I have the following code which is able to open an embedded Excel table in Word, change the A1 cell to the string "Testing", and close the embedded Excel.

So far this only runs for the first object in the document. I'd like it to run through the entire count of objects that it finds, as seen below. See 'ActiveDocument.InlineShapes.Count'. I realize right now it only finds 1 to 1 in the first For loop. I've experimented with some different things but can't get it right. I'm guessing that I need a Do while loop in there... Any help figuring this out?

Sub TestOpenEditandSave()

Dim oOleFormat As OLEFormat
Dim lNumShapes As Long
Dim lShapeCnt As Long
Dim xlApp As Object

'ActiveDocument.InlineShapes.Count

For lShapeCnt = 1 To 1 'ActiveDocument.InlineShapes.Count
    If ActiveDocument.InlineShapes(lShapeCnt).Type = wdInlineShapeEmbeddedOLEObject Then
        If ActiveDocument.InlineShapes(lShapeCnt).OLEFormat.ProgID = "Excel.Sheet.8" Then
            ActiveDocument.InlineShapes(lShapeCnt).OLEFormat.Edit
            Set xlApp = GetObject(, "Excel.Application")
            xlApp.Workbooks(1).Worksheets(1).Range("A1") = "Testing"

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

        End If
    End If
Next lShapeCnt

End Sub

This looks like a duplicate of https://stackoverflow.com/questions/483813/modify-embedded-excel-workbook-in-word-document-via-vba - the Answer by Gary McGill in there works here, including the deactivation routine, but it may be that the `Selection.Find` method works just as well. That method also works with `For Each`. The main difficulty I encounter is that you can't single-step through the code in the VBA debugger, because the VBE seems to do a "Continue" at a certain point. But you can still use breakpoints. — , Oct 01 '20 at 09:14

John Korchok · Answer 1 · 2020-09-30T19:21:34.363

0

It's a little simpler if you use the builtin collections in Word:

Sub FindShapes()
    Dim oInlineShape As InlineShape
    
    For Each oInLineShape In ActiveDocument.InlineShapes
        If oInlineShape.Type = wdInlineShapeEmbeddedOLEObject Then
            If oInlineShape.OLEFormat.ProgID = "Excel.Sheet.8" Then
                'Do stuff here
            End If
        End If
    Next oInlineShape
End Sub

edited Sep 30 '20 at 19:21

answered Sep 30 '20 at 17:10

John Korchok

4,723
2
11
20

Did you mean .ProgID vice .ProID? "Object variable or With block variable not set" error at line If oInlineShape.Type = wdInlineShapeEmbeddedOLEObject Then. Also looks like it has trouble with the Next oInlineShape line. – Mike Sep 30 '20 at 17:17
Spelling mistakes fixed. – John Korchok Sep 30 '20 at 19:25

VBA Word: Do While Loop

1 Answers1