asked on

Copy values from Excel cells into Word

Hello,

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.

jppinto

andrewssd3

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.
word-copy.xlsm
From-Excel.docx

jppinto

ASKER

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?

Regards,

jppinto

jppinto

ASKER

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.

ASKER CERTIFIED SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jppinto

ASKER

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