Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Copy values from Excel cells into Word


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.

  • 3
  • 2
1 Solution
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.
jppintoAuthor Commented:
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?


jppintoAuthor Commented:
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.
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

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now