Moving Excel data to Word template

[Cerberus262]

Here's what I'm trying to do:

1. I start with an Excel workbook as a "template", modify data and save as a new workbook.
2. Move data from the new workbook to a "template" Word file, both cell data and selected parts of sheets.
3. Save the Word file as a new Word file.

Essentially the Excel data file will be new each time, but the Word file will be the same "template" each time. That means I can't do a "Paste Link" from Word as Word will be using for the "template" Excel file each time. I need to start this from Excel.

I'm assuming there is a VBA way to make this happen, but I'm not familiar with VBA at all. Is this a monumental project?

I have attached versions of my "template" files to get an understanding of the data I'm trying to move. Right now I'm manually cutting/pasting into Word, but it's getting time consuming. Any help is appreciated.

[mrice]

You can do this by a series of find and replace operations.

Make a reference to the MS Word 14 object library using the tools menu in the VBA editor and use something like the following. This example just replaces the name of the example company in the word template withthe name from the first sheet in the workbook. By repeating this format, you should be able to construct code to do any replacements that you wish.

Please Login or Register  to view this content.

[Cerberus262]

Martin,

First - thanks for assisting! Second - please excuse my ignorance and some likely eye rolling questions. I am a complete rookie, but want to make this work.

I got the code into VBA editor and successfully managed to replace company name as directed. I consider this a big win for my skill level...

My issue now - in trying to cut/paste code for another find/replace, I can't get it to work. Should I be cutting/pasting the entire code for each instance or is there just a portion?

Second question - will this work for a selection, say A1:G57 of the BalSheet worksheet? And how much control over the size, etc in the Word file? I've always cut/pasted special as a bitmap as that's the easiest way to control the size of the sheet contents. Ideally I'd like to be able to port over a section of the spreadsheet to take a full page of the Word file in whatever format is best. Right now I'm just using a placeholder name in the Word file (BALANCESHEET) and trying to replace that with the selection noted above. Am I on the right track?

Again - thanks a bunch!

[mrice]

The section of code that you need to replicate for each replace operation is as follows with
the section in red changing each time. Add the replicated section just before the End Sub line.

Please Login or Register  to view this content.

My suggestion is that you copy each cell individually using a set of unique placeholders in your Word document if you want to keep things simple. There are alternative approaches which directly insert values into Word tables. The attraction of the replace approach is that you can set up all the formatting with the placeholder in Word and the code then just replaces one set of characters with another.

Hope that this helps.

[Cerberus262]

Thanks for clarifying the code I need to replicate. You are helping greatly!

So there's no easy way to take a selection from a worksheet and port to the Word template? Doing it cell by cell will be a lot, as there are 100 cells in one of the sheets alone. I need to do this for at least 4 sheets. I'd likely have to set up 400 or so variables.

[mrice]

You can do something more efficient by populating cells in a pre-existing table in your Word template.

Please Login or Register  to view this content.

The above code will take a 10 x 5 array of data from a sheet and put it into the first table in the Word document (which is also 10 x 5)