Export Excel sheet into Word template file

Ghost

I've modified your sub Open_Word_Document to show a way to add data from Excel into the (only) table in your Word document. The changes are in bold (apart from where I've deleted comments/ unnecessary lines) with some comments added for others:

Sub Open_Word_Document2()
    Set AppWord = CreateObject("Word.Application")
    AppWord.Visible = True

    Set WordDoc1 = AppWord.Documents.Open("c:\temp\WordTemplate.docx") 'document in sheet "word file"

    'write to table
    With WordDoc1.Tables(1)
        'Loop rows
        For iRow = 1 To .Rows.Count
            'Loop columns
            For iCol = 1 To .Columns.Count
                ' Clear cell and replace with text from offset cell
                With .Cell(iRow, iCol).Range
                    .Delete
                    .InsertAfter Text:=Sheet2.Range("Y2").Offset(iRow, iCol).Value
                End With
            Next iCol
        Next iRow
    End With
    'save new file
    WordDoc1.SaveAs Filename:="c:\temp\WordTemplate8.docx" 'new filename

    AppWord.Activate

AppWord.ActiveDocument.Close
AppWord.Quit

End Sub

Note that the result may look odd since the Excel cells for CustomerName  incldee a line return after the name. Also the Word template may need an extra column (to accommodate CustID but perhaps your customers don't need to see that). Actually I noticed that you wanted to export z:AC only so you need to change the code above to read:

'Loop first 4 columns only

For iCol = 1 To 4

I have not attached a file (since my test version used local file locations to check that the method worked) but the macro above is named ...2 so you can just paste it into your module and try.

VBA methods in Word are somewhat different to those in Excel so I suggest you investigate Word methods (I'm no expert!).

Hope this helps.