0
votes

[There is another recent question with a similar title that has not yet been answered satisfactorily, and gave me no insight :-( ]

This is my first attempt to integrate a little Word automation with Excel VBA, so I started with a macro recording in Word VBA to have a clue. Unfortunately, when I try to translate this into Excel, even making allowances for having to use the Excel local wrdDoc to qualify references, the code that works in Word VBA does not work from Excel VBA. Deeply frustrating.

Here is the recorded code snippet from Word that I started with: [Shape name manually edited after recording to match assigned name for the Watermark image]

ActiveDocument.Sections(1).Range.Select
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Selection.HeaderFooter.Shapes("US-CA watermark").Select
Selection.Delete

Here is the code snippet from Excel I derived from that: [sWatermark string predefined to match the picture name]

With wrdDoc
    .Sections(1).Range.Select
    .ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
    .Selection.HeaderFooter.Shapes(sWatermark).Select
    .Selection.Delete
End With

Result: VBA chokes every time when I try to select the watermark (line 3 within the With block).

I have tried the problem line with and without the . prefix [i.e. as wrdDoc.Selection and just Selection (as in the original Word macro) ].

I have tried accessing the watermark as .HeaderFooter.shapes(1) after verifying that there is only 1 item in the range. Again, this seems to work quite satisfactorily in Word VBA, but never in Excel VBA.

WHY?!?!?!

1
.Selection should be .Application.Selection - Slai

1 Answers

0
votes

Avoid using the Selection when possible, as it can be changed during macro execution. For example:

wrdDoc.StoryRanges(wdPrimaryHeaderStory).ShapeRange(1).Delete