Merging data from excel to word

[WordUser]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: intel

Hi

I have a 5 row, 4 column table in Word that is part of a larger layout. In Excel I have a 5 column spreadsheet with about 150 rows. I need the 5 fields in the spreadsheet (i.e which represent one complete row) to come into each of the COLUMNS in Word, ie. the first column would contain field 1, field 2, field 3, field 4 and field 5 - each in the 5 rows of the first column. Then each of the next 3 columns on the page would contain the next 5 fields (which represent 1 complete row) from the spreadsheet.

I've tried doing a data merge and placing the field codes in the five rows of the first column in Word. To try to get it to work I added a sixth row and inserted a <<next record>> field code, hoping that this would allow the next column to be populated with the next row of the spreadsheet. I also duplicated all the field codes from the first column and pasted them into the other 3 columns.

This method isn't working though.

When I do a merge to a separate document, the first column is getting populated correctly but the remaining 3 columns are just duplicates of this first column instead of being populated with the records from the next three rows from the spreadsheet.

I wondered if someone could tell me how I can get it working correctly - if it's possible at all.

Thanks

 

[CyberTaz]

Hi ________ -

I think I understand how the data is set up in Excel (a typical list) and
that you're essentially looking to have it Transposed from an Excel list
arrangement to a columnar arrangement in the doc - does this sound right?

Before putting any more thought into it though, help me understand something
that *isn't* at all clear Based on how I interpret your description you
want to have each row of data merged to a separate column in the table.
However, you have 150 records (rows) to merge & only 4 columns to merge into
- where are the other 146 records supposed to go? In all I'm seeing 750 data
items (5 X 150) and only 20 table cells (5 X 4) to put them in... The
numbers just don't work for me, and with no idea what's to happen with all
that additional data I have no idea how to proceed.

The way you're attempting to employ the Next Record field suggests that you
may expect Word to create additional "sets" of 5 rows each to accommodate
all that other data, populating the table with 4 records in the columns of
each set of rows. If I'm understanding correctly, that's going to take a bit
of magic along with a shamrock the size of Ireland itself... Or at least
a lot more expertise than I can lay claim to, if it can be done at all.

If I've misinterpreted please forgive me - it's early yet - and help me
understand where I'm going wrong.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[WordUser]

Thanks for replying,

<<I think I understand how the data is set up in Excel (a typical list) and
that you're essentially looking to have it Transposed from an Excel list
arrangement to a columnar arrangement in the doc - does this sound right?>>

Yes that's right.

<<Based on how I interpret your description you want to have each row of data merged to a separate column in the table.>>

Yes

<<However, you have 150 records (rows) to merge & only 4 columns to merge into - where are the other 146 records supposed to go? In all I'm seeing 750 data items (5 X 150) and only 20 table cells (5 X 4) to put them in...>>

I was thinking along the lines of how you do a merge with envelopes, labels or even form letters. You know how you can have a letter set up on one page and you have the field codes inserted, then when you trigger the merge to a new document the same form letter that was on the first page is duplicated to the other pages and the fields on each subsequent page are populated with the data from the spreadsheet.

I kind of thought that the same principle applied here. I have one page set up with the table consisting of the 4 columns and 5 rows, along with other text etc. on the same page. I thought that if I could get the first four rows (5 fields) from the spreadsheet merged into the four columns on this first page then Word would do the rest, ie. duplicate the content on this first page (including the table itself) and populate the fields in all the subsequent pages - just like it does with the envelopes, labels and form letters.

Is my thinking right?

 

[CyberTaz]

That's going to be very difficult - in fact it may be impossible... At least
without a lot more code than I know how to write. The problem is that Word
tables just don't work that way with merge. Envelopes & Labels are based on
templates & the amount of data just causes additional "sheets" to get
grabbed from the bin to accommodate it.

I've messed around using the Catalog merge method & can see a few things I
didn't realize had potential, but I can't make them work the way you want.
There are possibly other options but I'm just not sure what they are. If I
come up with any ideas I'll post back.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[WordUser]

Okay thanks for testing for me. I may need to resort to using some VBA to get it working - will have to do some more research.

 

[Daiya Mitchell]

What's your overall setup here, and what does the end result need to
look like? It sounds like all the data is going into a single table,
rather than merging specific pieces of data into a series of
individually personalized letters, but I can't quite tell.

For instance, you could merge the data into labels with the right number
of columns (as the label wizard is set up to merge into a table), then
massage it via the Table menu controls to get it into the exact table
format you want (possibly Convert Table to Text, find and replace to
eliminate blank lines, Convert Text to Table).

 

[WordUser]

Yes the data is going into a single table but there will be several tables once all the data is merged since there's only one table per page with 4 columns and 5 rows and therefore more pages would need to be added to the document in order to accommodate all the 150 rows of data from excel.

As CyberTaz has said, apparently you can't do a merge into a table with the setup I've explained previously which is why I may have to go the VBA way.

 

[Daiya Mitchell]

Good luck. I'd be interested in hearing how your solution worked out.

 

[CyberTaz]

I've done a little more messing around & found that you may not have to go
the VBA route after all - IF you're willing to make a bit of a compromise

The real obstacle you face with the current approach is that you're trying
to insert each Field in a separate cell. In addition it relies on Word to
expand the table based on the number of Records and that it won't do without
some rather feisty code (at least from my "non-coder's" perspective.

The "effect" you want can be obtained rather easily if having a complete
Record per cell is acceptable and you are willing to create the entire table
*first* so Word has someplace to put the incoming data. What works here is:

1- Create the table 4 columns by 150 rows,
2- Start the Data Merge Manage & specify Form Letter,
3- Select your Record Source,
4- In the 1st cell insert the first field, then pres Shift+return*, insert
the next field then Shift+return, etc. for each field of data (except after
the last field in the cell),
5- Select & copy the fields - Do Not include the End of Cell marker,
6- In the 2nd cell of that same row insert a NEXT RECORD field then paste
into the same cell immediately after the Next Record field,
7- Select & copy the content of that 2nd cell (again avoid the EOC marker),
8- Select the other 2 cells on that row & paste,
9- Select the rest of the rows & paste again.

*This is necessary instead of just pressing return because paragraphs in the
cells won't enable the pasting facility in steps #7 & #8 - otherwise each
field gets pasted into a separate cell.

When you hit the View Merged Data button you should see what you want.
Modify the table to suit, either in the Form doc or in the resulting doc
once you actually do the merge.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[WordUser]

Thanks for the instructions - I'll see if I can adapt things to get it working.