Link to home
Start Free TrialLog in
Avatar of gisvpn
gisvpnFlag for United States of America

asked on

Create word document from Excel File with Word Template

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
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

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

ASKER CERTIFIED SOLUTION
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial