CODEWITHSUNDEEP
vbaexcel
user971741
user971741

Reputation:

Append instead of replacing Word document contents

I have multiple sheets in an Excel workbook. Over which I am iterating in a loop, where I copy data ranges and pasting them into word.

Issue is every time the contents of the sheet are copied and pasted to word the previous all contents goes away.

So how can I stop replacing the previous content and make the VBA script append to the word document?

Sub ewc()

    Dim WordApp As Word.Application
    Dim myDoc As Word.Document
    Dim WordTable As Word.Table
    Dim ws As Worksheet
    Dim tbl As ListObject

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Set WordApp = GetObject(class:="Word.Application")

    WordApp.Visible = True
    WordApp.Activate

    Set myDoc = WordApp.Documents.Open("D:\asd.docx")

    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name, ThisWorkbook.Worksheets.Count

        ws.UsedRange.Activate
        LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row
        LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column
        Debug.Print LastRow, LastColumn

        ws.Range(StartCell, ws.Cells(LastRow, LastColumn)).Select
        myDoc.Content.Paste

        Set WordTable = myDoc.Tables(1)
        WordTable.AutoFitBehavior (wdAutoFitWindow)
        myDoc.Save


EndRoutine:
        Application.ScreenUpdating = True
        Application.EnableEvents = True

'Clear The Clipboard
        Application.CutCopyMode = False
    Next ws
End Sub

Upvotes: 2

Views: 414

Answers (1)

arcadeprecinct
arcadeprecinct

Reputation: 3777

myDoc.Content.Paste replaces everything in myDoc.Content.

If you don't want to replace the contents of the range, use the Collapse method before using this method.

Since you can't collapse myDoc.Content, you need a custom range object

Dim pasteRange as Word.Range
'...
Set pasteRange = myDoc.Content
pasteRange.Collapse wdCollapseEnd
pasteRange.Paste

Upvotes: 3

Related Questions