how to auto populate and create a word document using VBA from an excel spreadsheet

I have been contracted to create a database for a company. They requested a spreadsheet with the different information. I got that. What i'm having troubles with is the programming of a macro that will copy the data from the excel sheet, open a specific word document and then paste it in the word document. I have figured out how to open a word document from within excel from this website (http://www.exceltip.com/applications-word-outlook-in-vba/control-word-from-excel-using-vba-in-microsoft-excel.html) but after that i get stuck. If anyone knows the code or how/where to get the code for this to happen that would be great.
 

Question Info


Last updated January 8, 2020 Views 24,899 Applies to:

Hi,

Borrowing heavily from that link you posted, take a look at the code below.

 

Sub PopulateWordDocFromExcel()
' Add a reference to the Word-library via VBE > Tools > References > Microsoft Word xx.x Object Library.
' Create a folder named C:\Temp or edit the filnames in the code.
'
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim bWeStartedWord As Boolean
   
    Dim i As Integer
   
    On Error Resume Next
    Set wrdApp = GetObject(, "Word.Application")
    On Error GoTo 0
    If wrdApp Is Nothing Then
        Set wrdApp = CreateObject("Word.Application")
        bWeStartedWord = True
    End If
    wrdApp.Visible = True 'optional!
   
    Set wrdDoc = wrdApp.Documents.Add ' create a new document
    ' or open an existing document:
    'Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\Filename.docx")
   
    ' example word operations:
    With wrdDoc
        'copy data from A1:A10 into the word doc:
        For i = 1 To 10
            .Content.InsertAfter Range("A" & i).Value
            .Content.InsertParagraphAfter
        Next i
       
        wrdApp.DisplayAlerts = wdAlertsNone
        .SaveAs "C:\Temp\MyNewWordDoc.docx", FileFormat:=12 'wdFormatXMLDocument
        .Close ' close the document
        wrdApp.DisplayAlerts = wdAlertsAll
    End With
   
    If bWeStartedWord Then wrdApp.Quit 'close the Word application
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
End Sub

 

If there are other things you want to do in Word, just record a macro in Word and translate the code over to this macro using wrdApp instead of Application and wrdDoc instead of ActiveDocument.

 

Any questions?

Cheers
Rich

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Depending on what's in the existing Word document and where in that document you want to put the data from the Excel sheet, you might do better converting the document into a mail merge main document and using the spreadsheet as the mail merge data source.

For an introduction, see http://www.gmayor.com/merge_labels_with_word_2007.htm. (It isn't just about labels.) If you need more than the basic merge functions, get the add-in from http://www.gmayor.com/ManyToOne.htm.
_____________________________
https://jay-freedman.info

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hope this helps,
Doug Robbins - MVP Office Apps & Services (Word)
dougrobbinsmvp@gmail.com
Screen shots by Snagit from www.techsmith.com

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

For the mail merge what type of merge would you suggest?

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

If you look at my web page http://www.gmayor.com/ManyToOne.htm  suggested by Jay, it uses a Letter merge format, but beyond that it does much more that is explained in the page.

As you have not really indicated what it is that you are extracting from, Excel, into what kind of document it is hard to make firm suggestions. Are you trying to grab data from just one record to create (say ) a letter? In which case the userform would be the better approach. It is relatively simple to fill a userform list or combo box with the contents of a worksheet, displaying only one column and write the values from this and the other hidden columns into the document as appropriate.

If you are producing several documents at a time then go with mail merge.
Graham Mayor (Microsoft Word MVP 2002-2019)
For more Word tips and downloads visit my web site
https://www.gmayor.com/Word_pages.htm

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks for helping, now i have a new issue. I cant figure out how to get it to paste into bookmarks ive 
placed in the word doc

here is my code

Sub PopulateWordDocFromExcel()
' Add a reference to the Word-library via VBE > Tools > References > Microsoft Word xx.x Object Library.
' Create a folder named C:\Temp or edit the filnames in the code.
'
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim bWeStartedWord As Boolean
    
    Dim i As Integer
    
    On Error Resume Next
    Set wrdApp = GetObject(, "Word.Application")
    On Error GoTo 0
    If wrdApp Is Nothing Then
        Set wrdApp = CreateObject("Word.Application")
        bWeStartedWord = True
    End If
    wrdApp.Visible = True 'optional!
    
    Set wrdDoc = wrdApp.Documents.Open("C:\Paul Davis\mkt.docx")
    ' or open an existing document:
    'Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\Filename.docx")
   
      ' example word operations:
    With wrdDoc
        ' copy company name
        Range("J2").Select
        Selection.Copy
        word.ActiveDocument.Bookmarks("bkname").Range.PasteAndFormat
        wrdApp.DisplayAlerts = wdAlertsNone
        .SaveAs "C:\Paul Davis\mkt.docx", FileFormat:=12 'wdFormatXMLDocument
        .Close ' close the document
        wrdApp.DisplayAlerts = wdAlertsAll
    End With
   
    
    If bWeStartedWord Then wrdApp.Quit 'close the Word application
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
End Sub

I did some research online and found this line of code
     word.ActiveDocument.Bookmarks("bkChart1").Range.PasteAndFormat
but when its inserted it does not work. I get the error code 'argument not optional'
Also i'm not copying lists of cells just specific ones which each are then pasted into different bookmarks

Any ideas? thanks.

EDIT

I just changed the code up a bit and it copied and pasted correctly but destroyed the format of the word doc.
Code for the section i changed:
With wrdDoc
        ' copy company name462
        Range("J2").Select
        Selection.Copy
        wrdDoc.Bookmarks("bkname").Range.Paste
        wrdApp.DisplayAlerts = wdAlertsNone
        .SaveAs "C:\Paul Davis\mkt.docx", FileFormat:=12 'wdFormatXMLDocument
        .Close ' close the document
        wrdApp.DisplayAlerts = wdAlertsAll
    End With

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi,

1) You need to specify the type when using PasteAndFormat, hence you getting the "argument not optional" error.
See http://msdn.microsoft.com/en-us/library/office/bb241746(v=office.12).aspx
(MS "Help" at its finest there!")

 

2) No need to use the Selection object.
In general, don't do this:

    Range("J2").Select
    Selection.
Copy

do this:
    Range("J2").Copy
your code will run much faster!

 

But...
3) Why use the clipboard at all? 
First, have a read of http://word.mvps.org/faqs/macrosvba/WorkWithBookmarks.htm to understand bookmarks in Word.
Why not just do this:

 

 

Sub PopulateWordDocFromExcel()
' Add a reference to the Word-library via VBE > Tools > References > Microsoft Word xx.x Object Library.
' Create a folder named C:\Temp or edit the filnames in the code.
'

    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim wdBmRng As Word.Range
   
    Dim bWeStartedWord As Boolean
    Dim i As Integer
   
    On Error Resume Next
    Set wrdApp = GetObject(, "Word.Application")
    On Error GoTo 0
    If wrdApp Is Nothing Then
        Set wrdApp = CreateObject("Word.Application")
        bWeStartedWord = True
    End If
    wrdApp.Visible = True 'optional!
   
    Set wrdDoc = wrdApp.Documents.Open("C:\Paul Davis\mkt.docx")
    ' or open an existing document:
    'Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\Filename.docx")

  
      ' example word operations:
    With wrdDoc
        ' copy company name462
        On Error Resume Next 'or could use On Error GoTo ExitPoint
        Set wdBmRng = wrdDoc.Bookmarks("bkname").Range
        On Error GoTo 0
        If Not wdBmRng Is Nothing Then
            wdBmRng.Text = Range("J2").Value
        Else
            MsgBox "Bookmark not found!"
            Stop
'to debug, or use  Exit Sub  etc....
        End If
        wrdApp.DisplayAlerts = wdAlertsNone
        .SaveAs "C:\Paul Davis\mkt.docx", FileFormat:=12 'wdFormatXMLDocument
        .Close ' close the document
        wrdApp.DisplayAlerts = wdAlertsAll
    End With
   
ExitPoint:
    If bWeStartedWord Then wrdApp.Quit 'close the Word application
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
End Sub

 

 

Cheers
Rich

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

once agin thanks for all your help. It works and now copys to the correct place. 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Letting users specify technical details is often not a good idea.

What is it they are trying to accomplish?  Do they want to create a "dictionary" of "boilerplate" / "standard" text that gets pasted together into various "letters" based on some sort of triggers?
All mushrooms are edible.
Some are only edible once.

¯\_(ツ)_/¯

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.