Copying data from Excel to Word

Here is a suggestion. Make a sheet on your spreadsheet called "MailMerge" (or whatever makes sense to you).

In row 1, put in meaningful headers for the data you want to put on the word document.
In row 2 type in formulas to fill in the cells with data such as ='Sales'!$D$14

Then use this sheet as the source for a mail merge in the word document. I might be one record long by 100 columns, but it should work.

Originally Posted by dflak

Here is a suggestion. Make a sheet on your spreadsheet called "MailMerge" (or whatever makes sense to you).

In row 1, put in meaningful headers for the data you want to put on the word document.
In row 2 type in formulas to fill in the cells with data such as ='Sales'!$D$14

Then use this sheet as the source for a mail merge in the word document. I might be one record long by 100 columns, but it should work.

No, it won't - not unless the OP wants each row on a different page, together with all of whatever exists in the document either side of the row's data. There are ways to output multiple rows to a single page via mailmerge, but this isn't it. In this case, though, it's not even evident a mailmerge is required - a DATABASE field in the Word document may suffice. For a demonstration, see: http://www.msofficeforums.com/mail-m...html#post67097

I think that depends on the type of mailmerge you want to do: labels, document, etc. I'm doing this at work with one of our technical writers. He has a canned 100 or so page report that reads in about 150 metrics.

I appreciate the replies, I came across something on google earlier about mail merge and I managed to incorporate it into my spreadsheet and word template but, it just didn't work with the word template I have as it didn't keep the structure and moved things around. I was using bookmarkers.

Originally Posted by macropod

No, it won't - not unless the OP wants each row on a different page, together with all of whatever exists in the document either side of the row's data. There are ways to output multiple rows to a single page via mailmerge, but this isn't it. In this case, though, it's not even evident a mailmerge is required - a DATABASE field in the Word document may suffice. For a demonstration, see: http://www.msofficeforums.com/mail-m...html#post67097

Paul thank you for the reply I shall look into this when I have a bit more time later on.

On another note, I would like to ask a quick question. I was wondering if anyone knew if I could paste special to word in HTML format through vba? I have a code that will paste what I need to word but the current code doesn't retain the look of the template on excel.

I have tested the paste special without vba (manually) and it seems to work, but I wouldn't know how to write this into excel.

This is the code I am using

Please Login or Register  to view this content.

Thank you

Gus

Originally Posted by dflak

I think that depends on the type of mailmerge you want to do: labels, document, etc. I'm doing this at work with one of our technical writers. He has a canned 100 or so page report that reads in about 150 metrics.

How about a letter with a table of the Excel data? You cannot do that with an ordinary mailmerge.

Originally Posted by mango2015

I appreciate the replies, I came across something on google earlier about mail merge and I managed to incorporate it into my spreadsheet and word template but, it just didn't work with the word template I have as it didn't keep the structure and moved things around. I was using bookmarkers.

....

On another note, I would like to ask a quick question. I was wondering if anyone knew if I could paste special to word in HTML format through vba? I have a code that will paste what I need to word but the current code doesn't retain the look of the template on excel.

Perhaps if you could explain exactly what you're trying to achieve, specific advice could be given. So far, what you've posted isn't enough to decide whether mailmerge, a DATABASE field, object linking and/or a macro might best meet your needs.

What you could do, given that the data from Excel always originates in the same set of cells, is to copy each block of Excel cells, then paste them into Word using Paste Special with the 'Paste Link' option and whichever of the Paste Link formats best meets your needs. No code required. Simply open the Word document and there the current data will be. Presumably, though, you'll want to save a separate copy of the document for each order and not have it updating every time the Excel data change. In that case, a small amount of code will be required and, instead of saving the document as a document, you'd save it as a Word template (.dot format). In that case, all the code you might need is:

Please Login or Register  to view this content.

The above code would generate & save a new Word document with the Order# in the same folder as the workbook. Simply change 'MyTemplate' to the full path & name of your template.

The commented-out lines would print and close the new Word document, respectively.

Was your template saved in the .dot format?

Since you had saved in dotx format, you could have just added the 'x' to the template's extension in the code.

As for the data not appearing, did you use the copy/paste special process before saving the template, so as to set up the links?

Double-click on the template. This will cause Word to create a new document from it. Then press Alt-F9. Do you see anything like:
{ LINK Excel.Sheet.8 "C:\\Users\\Mango2015\\Documents\\Copying-data-from-excel-to-word-example.xls" "PO Template!R9C9:R9C12" \a \f 4 \h }

Which means the template never had any links to the Excel workbook. You need to re-open the template for editing then do the copy/paste-link process I outlined in post #10: http://www.excelforum.com/showthread...=1#post4341128

You could copy Excel cells A10-A15 as a group and paste them into the corresponding part of the Word table as a linked unit. You might equally copy Excel cells I9, I12 and I16 and paste them into the corresponding parts of the Word table as individual linked units.

Your reply suggests you're trying to paste the entire Excel 'table' into Word. Sure, you could do that, but I was only suggesting you paste the content of the cells you need to replicate.