Create word document from Excel File with Word Template

gisvpn
gisvpn used Ask the Experts™
on
Hi All,

I was looking for help. I have an excel file which has cells of information which I would like to copy into a Word document which already exists and is essentially a template which I would like to copy information from the Excel document into the word template.

I have looked over the internet and found similar things but not what I am looking for.

Could someone help me with this and whether this can be done - I have attached the documents (word template and excel document) they will always reside in the same folder.

As a walk through I would like the macro (vba would be great) to do this.

Open the word template
copy the title from cell B2 in excel into the word document just after where it says Title on the first page

Copy the date from cell B3 into the word document in the cell next to where it says Date

Copy the range A1:D10 as a table into the space where it says <table to insert here> in the word document.

Copy the cell B6 from excel into the word document under where it says Header 1

Copy the cell B9 from excel into the word document under where it says Header 2 and then copy the cell B10 as a paragraph under the one copied just before.

Thank you!!
excelfile.xlsx
wordfile.doc
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
GrahamSkanRetired
Top Expert 2012

Commented:
That seems to be multiple questions in one.

You really need to have target positions on the Word document defined in some way. There are several ways to do that. One (which you use for the date) is a particular table cell. Another way is to use bookmarks. However I would recommend that you use Content Controls.

Here is some code to get you started.
   
Option Explicit


Sub CopyToDoc()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim wks As Worksheet
    Dim bNewInstance As Boolean
    
    'try to use existing instance of application
    On Error Resume Next
        Set wdApp = GetObject(, "Word.Application")
    On Error GoTo 0
        
    If wdApp Is Nothing Then
        'no existing application, so create a new one
        Set wdApp = CreateObject("Word.Application")
        bNewInstance = True
    End If
    wdApp.Visible = True
    
    Set wdDoc = wdApp.Documents.Open("C:\MyFolder\Mydoc.doc")
    
    Set wks = ActiveWorkbook.Sheets(1)
 
    'modify the document
    
    'copy the date
    wdDoc.tables(1).Cell(1, 2).Range.Text = wks.Cells(3, 2).Value
    '...
    
    wdDoc.Close wdSaveChanges
    
    'close application if specially created for this code
    If bNewInstance Then
        wdApp.Quit
    End If

End Sub

Open in new window

Retired
Top Expert 2012
Commented:
I have now added a Content Control and a bookmark to the Word document to show how each can be used. The document has now been saved as an XML template, so the code now creates a new document from the template.

The content control accepts the text for the Title, and the bookmark is where the table is created.
Sub CopyToDoc()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim wks As Worksheet
    Dim ccs As Word.ContentControls
    Dim wdRange As Word.Range
    Dim tbl As Word.Table
    Dim bNewInstance As Boolean
    Dim r As Integer
    Dim c As Integer
    
    'try to use existing instance of application
    On Error Resume Next
        Set wdApp = GetObject(, "Word.Application")
    On Error GoTo 0
        
    If wdApp Is Nothing Then
        'no existing application, so create a new one
        Set wdApp = CreateObject("Word.Application")
        bNewInstance = True
    End If
    wdApp.Visible = True
    
    Set wdDoc = wdApp.Documents.Open("C:\MyFolder\wordfile.dotx")
    Set wks = ActiveWorkbook.Sheets(1)
 
    'modify the document
    
    'copy the date
    wdDoc.Tables(1).Cell(1, 2).Range.Text = wks.Cells(3, 2).Value
    'copy the title text to a content control
    Set ccs = wdDoc.SelectContentControlsByTitle("TitleText")
    ccs(1).Range.Text = wks.Cells(2, 2).Value
    'ceate table at a bookmark and fill it
    Set wdRange = wdDoc.Bookmarks("bmkTable").Range
    Set tbl = wdDoc.Tables.Add(wdRange, 10, 2)
     
    For r = 1 To 10
        For c = 1 To 2
            tbl.Cell(r, c).Range.Text = wks.Cells(r, c).Value
        Next c
    Next r
    
    'wdDoc.SaveAs
    
    'close application if specially created for this code
    'If bNewInstance Then
        'wdApp.Quit
    'End If

End Sub

Open in new window

wordfile.dotx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial