Copy values from Excel cells into Word

Posted on 2012-08-13
Last Modified: 2012-08-15

I need to automate a task of copying values from 4 cells in an Excel sheet into a Word template. It could be when the user clicks a button on the Excel sheet that the macro copies the values from these 4 sheets into specific places on the Word document.

What is, in your opinion, the best way of doing it? Create a link between the two documents or, using a macro, copy the values into the Word file? Can you please post an example on how to do it with a macro?

Thanks in advance for your help.

Question by:jppinto
    LVL 17

    Expert Comment

    There are lots of ways to do this, but here's some sample code.  The idea is you have some named ranges in Excel called 'WordSource1', 'WordSource2', etc.  In the Word document you define bookmarks called 'ExcelData1', 'ExcelData2', etc.  The when you press the button on the Excel sheet it copies the fields across - 1 to 1, 2 to 2 etc. In the example the word destination fields are in a table, but they could be anywhere.

    At the moment the macro keeps the Word doc open so you can see, but it could save and close if that's what you want.  This is a VBA solution that does what you asked.  It is also possible to have pasted links into Word that can be updated when you want, using copy  then paste special...paste links, but this puts the overhead of OLE linking on the files.  I generally find it cleaner to copy once using VBA, then just the values are there with no residual link between the files.
    LVL 33

    Author Comment

    Hi Andrew,

    Thanks for your help. I get an user defined type error on this line:

    Dim rng As word.Range

    Can you see what's wrong?


    LVL 33

    Author Comment

    I've tryed changing to just this:

    Dim rng As Range

    made some modification to your code and it's working. But I have a problem with the format of the numbers that are being passed into Word. The values are currency (£) but I can't seem to pass the values on the correct formatting.
    LVL 17

    Accepted Solution

    The error id because you need a reference in your vba project to Microsoft Word - then you do need to change that back to Word.Range, otherwise it may get confused and think it's an Excel range.  Sorry forgot to mention this.

    You can get the formatting by using the .Text property rather than .Value on line 34 below:
    Public Sub CopyFields()
        ' prefix for fields to be copied to word
        Const EXCELFIELDS As String = "WordSource"
        ' prefix for bookmarks in word
        Const WORDFIELDS As String = "ExcelData"
        Dim strBmkName As String
        Dim rng As Word.Range
        Dim n As Excel.Name
        Dim i As Long
        Dim itemNo As Long
        Dim wdDoc As Word.Document
        Set wdDoc = GetObject("c:\users\stuart\documents\from excel.docx")
        ' look for names to be transferred to word
        For i = 1 To ThisWorkbook.Names.Count
            Set n = ThisWorkbook.Names(i)
            If n.Name Like EXCELFIELDS & "*" Then
                itemNo = CLng(Mid$(n.Name, Len(EXCELFIELDS) + 1))
                ' get the relevant word bookmark
                strBmkName = WORDFIELDS & CStr(itemNo)
                ' error handle in case it does not exist in Word
                On Error Resume Next
                Set rng = wdDoc.Bookmarks(strBmkName).Range
                On Error GoTo 0
                ' if it was found, put it in
                If Not rng Is Nothing Then
                    rng.Text = n.RefersToRange.Text
                End If
            End If
        Next i
        wdDoc.Application.Visible = True
    End Sub

    Open in new window

    LVL 33

    Author Closing Comment

    Excelent work Andrew! VBA is not my strongest skills! ;)

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now