Using Excel vba to stuff a new page into an opened Word document

Topic

New Reply

Hi All

This is an Excel vba coding help request. Please don’t send me to the Word forum!

I am trying to use Excel vba to insert a new page into an already opened Word document, DocA.

My DocA has been opened and successfully updated from Excel using Excel vba.

I am using bookmarks in DocA. I have a bookmark at the required insertion point for the new page.

For my new page, I have Excel open a single-page template DocB, update a couple of entries in the new page with data from Excel, and now want to stuff this page into DocA at the insertion point, which is bookmarked as “putHere”.

This is where I’m at:
xxx code working up to here xxx
With zDocA.Bookmarks
.Item(“putHere”).Select
‘what code do I need here to stuff my DocB contents???
End With

Any help most appreciated.

zeddy

Reply | Quote

Replies

Zeddy,

Here’s the code that works in Word so you should be able to use it with the Word Object in the Excel VBA.

Code:

Option Explicit

Sub StuffIt()
'
' StuffIt Macro
'
'
    Windows("DocB.docx").Activate
    Selection.WholeStory
    Selection.Copy
    Windows("DocA.docm").Activate
    Selection.GoTo What:=wdGoToBookmark, Name:="putHere"
    Selection.MoveRight Unit:=wdCharacter, Count:=2 'Move 2 spaces to Right (optional)
    Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:= _
        wdInLine, DisplayAsIcon:=False
End Sub

Note: On DocA you’ll want to change it to .docx I needed the .docm to run the macro from.
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

Many thanks for your prompt reply.
I’m very nearly there.

I have this:
xxx code working up to here xxx
ActiveDocument.Select
zWord.Selection.WholeStory
zWord.Selection.Copy

With zDocA.Bookmarks
.Item(“putHere”).Select
Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:= _
wdInLine, DisplayAsIcon:=False

End With

..but it is debugging on the Selection.PasteSpecial line
..so I guess my Selection is probably being treated as an Excel Selection??

zeddy

Reply | Quote

Hi RG

OK. I got it to paste the data by using..
zWord.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:= _
wdInLine, DisplayAsIcon:=False

But, although the complex footers of DocA are maintained for the newly ‘inserted’ page, including correct updating of page numbers, it didn’t quite maintain the formatting as per the DocB appearance.
Any suggestions??

zeddy

Reply | Quote

Hi RG

I got the required formatting by using..
zWord.Selection.PasteSpecial Link:=False, DataType:=wdPasteRTF, Placement:= _
wdInLine, DisplayAsIcon:=False

Yippee!

zeddy

Reply | Quote

Zeddy,

You got me on the formatting thing. I haven’t exercised Word since I retired 13 years ago! Glad I could help with the VBA though. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

You helped me focus.
But it turned out I didn’t get my formatting after all.
I was looking at the previous pages in the doc.
Back to square 2.

zeddy

Reply | Quote

so you retired at 30 then?
OK for some.

zeddy

Reply | Quote

so you retired at 30 then?
OK for some.

zeddy

I only wish! 😆 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

..I’ve now got my formatting. The page was inserted by Excel at the specified bookmark location.
But I now realise that the bookmark location in Word doesn’t seem to ‘move’ with it’s original setting, i.e. the bookmark “putHere” stays at the same ‘page location’. Which means, it is now where the new page was added.
If I want to repeat and insert another ‘page’, it ‘pushes’ the first new page down, rather than insert the new page after the first newly added page.

Any suggestions????

zeddy

Reply | Quote

Zeddy,

You’ll have to experiment how much to move but:

Code:

    Selection.MoveRight Unit:=wdCharacter, Count:=2 'Move 2 spaces to Right (optional)

 '  change to

    Selection.MoveLeft Unit:=wdCharacter, Count:=1 'Note it may take 2

This should cause the insertion to be BEFORE the bookmark.
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

That was what I was looking for.
I shall try it out tomorrow and report back then.

zeddy

Reply | Quote