# Using Excel NOT for Mail Merge but Merging Data into Word



## ibetrue (Feb 13, 2008)

I am trying to merge Excel spreadsheet data into multiple areas into a Word document. I cannot figure out on our 2003 Office version if I am missing a step or not...every time I "merge" it tells me it is merging hundreds of fields (we only have about 60 rows and 15 columns of data). 

When the merge is done it inserts one row of data multiple times. I know there is a way to tell it to only pull from certain areas (like a data range) but the selection is "greyed out". I've tried the "if" statement to pull information by department and that didn't work either. I've included a PDF of the spreadsheet and the final document (no "real" data is inputted) just to give you a clue as to what I am trying to accomplish..which may be a pipe-dream at this point.

At this stage I'm about willing to let my staff hand-enter the information into Word, but there's got to be a way to do this. MS Office help, is no help - they only talk about merging if you are doing a mail/letter merge. Thanks for any assistance!


----------



## CastleHeart (May 4, 2002)

Can't you just INSERT the selection you want ?

- Castleheart


----------



## Leza (Oct 18, 2007)

What about a copy and paste with a link? This will automatically update when the excel sheet is updated. 
Select the Excel cells...copy...move to location in Word...Edit menu...Paste Special...PasteLink...choose formatting
I hope this helps!


----------



## Rollin_Again (Sep 4, 2003)

Your best option is to add form fields to your Word document and then use a macro (VBA) to read each line in the spreadsheet and plug the appropriate cell values into their corresponding form fields. After each line is processed the macro would save the Word doc to your specified location with a unique name and then proceed to process the next line in the Workbook to repeat the process.

If you zip and post both the Word document and the Excel workbook I can help with the coding. 

Regards,
Rollin


----------



## slurpee55 (Oct 20, 2004)

Another alternative would be to deal with the lists as labels - see attached for a basic idea.

(You will have to place the Excel file somewhere and look for it as the data source when you open the merge file "Labels." The other word file should give you an idea of how it looks - if you can't get the merge to work).


----------



## ibetrue (Feb 13, 2008)

I'll try all those great Ideas - and get back to you which one works the best for our needs - thanks for being so candid!


----------



## ibetrue (Feb 13, 2008)

Thanks for the input Leza - It would be a perfect fix but in certain areas of the document we don't need all the info and certain places we'd just want two or three. I tried it ---and then there's the issue of transferring the "table to text", totally hate having tables in Word. Thanks again!


----------



## ibetrue (Feb 13, 2008)

Slurpee - Okay, I'm interested in your idea because it's more what I'm looking for...but like Leza's fix, I'm not sure how the "labels" will fit into my preferred, pre-formatted document. So, tell me, like "tables to text" is there something like that for a text in a label format? Thanks for any input!


----------



## ibetrue (Feb 13, 2008)

Rollin - Okay - I've done some form work but I am concerned about the Macro side (heard rumors at Excel trainings in the past that it's too easy to screw up your computer with them). FYI, the Excel document is an ever changing document and the Word doc is just a "template" or as you put it, form. Will that mess up the macros? When I'm back at my desk - I'll send out the Word/Excel files.


----------



## jimr381 (Jul 20, 2007)

Your document is using 2 mail merges. One pulling in information for the data at the top and then another pulling in birthdates into a table at the bottom representing labels.


----------



## ibetrue (Feb 13, 2008)

Jimr - yep, is Word smart enough for that in a document which isn't a letter nor labels?


----------



## ibetrue (Feb 13, 2008)

Thanks for offering your assistance.

Here are the two documents....mind you I have about 60 employees who fit into different departments (see first page of Word document). This is just for me to show you data without revealing personnel information. Format of how I'd like the Word "form" to look is below each department title. In a perfect world Word would "know" from the department/division on the spreadsheet and place it in the proper position in the form.

Thanks again!


----------



## Rollin_Again (Sep 4, 2003)

Can you post a couple more sample Word document with the source data already filled in so that I know how it should look?

Regards,
Rollin


----------



## ibetrue (Feb 13, 2008)

Thanks


----------



## slurpee55 (Oct 20, 2004)

I am thinking you will need to radically rework how your data is laid out if you want to accomplish this in Word...I am trying to get my head wrapped around how, though, and coming up with nothing useful.


----------



## jimr381 (Jul 20, 2007)

Two word documents doing mail merges and a master document combining the two.


----------



## slurpee55 (Oct 20, 2004)

Yes, I agree with jimr - the two halves of the document are radically different.
But to get the info into place depending on what job position the person has - not exactly an easy task.
You need more columns of data with labels like "Manager", "Manager phone", etc. and the appropriate people in those columns, another with "Sales" listings and the appropriate people in those columns....


----------



## Rollin_Again (Sep 4, 2003)

I agree with Slurpee who agrees with Jimr who would probably agree with Zack B if he was here 

The way the data is currently structured in the spreadhsheet there would need to be lots of unstructured logic written into the code to accomodate for each scenario. In order to use a macro to process the information each record would need to have more identifying information associated with it and each record must follow the exact same format. Currently there is no set way of identifying what department each person is with since the values in the Dept/Division column do not exactly match the departments listed on the Word doc. Like Slurpee already mentioned you should also include a seperate column to indicate whether the person is a manger or not instead of simply using the word "manager" as part of their title. To be 100% honest with you this is an ideal situation for a database application such as MS Access. Access allows you to create custom reports that will display all your records in a logical way without the need for macros or labels.

Regards,
Rollin


----------



## slurpee55 (Oct 20, 2004)

Sigh...I agree with rollin (who agrees with me who agrees with...) - but particularly about it being ideal for Access. That was my thought long ago, but I was trying to make this work.
I'm not sure that you couldn't even add another worksheet and imitate a document with it, drawing the data from the other worksheets. But where's firefytr when we need him? Or Anne?


----------



## jimr381 (Jul 20, 2007)

This would be pretty easy to do in Access with a report and a subreport and much easier to track in the long run. Would you like us to look into this route? You could even have it export it over to Word in the end as well.


----------



## ibetrue (Feb 13, 2008)

:up: You all are so very helpful! I was afraid the answer was going to turn into something like this (sigh). It seems that the standard Office products are not up to my pipe-dreamin', wish it was that easy.

I'll look into Access and see what we can to manipulate the information accordingly. Database managers still rule the roost when it comes to manipulating data but I thought being that there were under 100 employees we could get it to "work" another cheap and easy way.

I'm glad that it wasn't as simple as "just press this", now I realize that I truly did have a stumper for all of you. I'll pass the word on that if you want to find a solution, this is the place. 

Thanks again!
Ibetrue


----------



## slurpee55 (Oct 20, 2004)

Hey, if you do want it done in Access, be sure to come back - between jimr and rollin and maybe an odd assist from me (and if OBP joins the gang) well, you could count on a well-designed database that would have the reports done as you need them quickly.
It is always a matter of organization - whether in a database or in Excel or even in Word. The data you have could be used, but not as it is currently laid out, that is all - a mail merge - or a database, for that matter - requires that items that you want to separate be in different columns, or that there be a columns indicating the difference. To distinguish managers from non-managers, for instance, you could either have 2 columns:
Name|Title|
or a different 2
Name and Title|Manager yes/no|
There are ways to draw out titles, say, if they are separated from the name by a title, but essentially you are still creating another column. Best to organize you data in such a manner in the first place.

Good luck!


----------

