Avatar of gisvpn
gisvpn
Flag 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
Microsoft ExcelMicrosoft OfficeMicrosoft Word

Avatar of undefined
Last Comment
GrahamSkan

8/22/2022 - Mon
GrahamSkan

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
GrahamSkan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23