Data merge

[NIA2]

Hi All,

I have a table in Word with 4 rows and 4 columns. In Excel I have a 4 column spreadsheet and I want to merge the data form the spreadsheet into the Word document. I've set up a data merge in Word and have inserted the field codes that relate to the 4 columns in the Excel spreadsheet into the first 4 rows of the table in Word. I then copied these 4 field codes and pasted them into the other rows of the other 3 columns in the table.

I was hoping that when I merged to a separate document that the first row in the spreadsheet would merge into the first column, the second row into the second column, the third into the third and the fourth into the fourth. Then I thought it would automatically create a new page and the rest of the spreadsheet rows would merge into the columns of the new pages.

This didn't happen though. Instead it used 1 page for each row of the spreadsheet and just copied the values of the first column into the other 3 columns so there were 4 instances of each row of the spreadsheet on each page.

Is it possible to have the data merged as described above instead of what's happening now?

Thanks for any help.

 

[SkipVought]

Hi,

Each source data record has 4 fields.

Once you place each of these 4 fields in your document in ROW 1 of your table, you have to advance the source data to the next record.

In the Mail Merge Toolbar is a Insert Word Field button. There's where you find the [n]NextRecord[/b] field, that you can put at the beginning of each subsequent ROW in your table.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NIA2]

Thanks for the reply,

Each of the 4 fields are in one column of the table (and therefore 4 rows). There are another 3 columns in the table where another 3 records (ie. 4 fields per column) need to go on any one page. So in the first column I have the four fields and then in the next three columns I've added <<next record>> fields, but this hasn't worked either. When I merge to a new document, it's only merging data to the first column (not the other three columns). Do you know what I'm doing wrong?

 

[SkipVought]

"...then in the next three columns I've added <<next record>> fields..."

The NextRecord field is needed to move from one source data ROW to the next. You would NOT use that in column 2; rather in ROW 2 and folowing!!!
[tt]
| <<field1>> | <<field2>> | <<field3>> | <<field4>> |
|<<NextRecord>>
<<field1>> | <<field2>> | <<field3>> | <<field4>> |
.....
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NIA2]

Okay I understand that NextRecord is needed to move from one source data row to the next, but I'm trying to explain that my Word table is set up in a different way - I need each source data ROW to be inserted into each table COLUMN at a time - NOT row. So is there any way to do this?

 

[SkipVought]

Yo want the data TRANSPOSED?

I think that you'll have to read it in first and then transpose. Reading goes left to right, top to bottom.

OR...

Red the data in like this...
[tt]
| <<field1>> |
| <<field2>> |
| <<field3>> |
| <<field4>> |
| <<NextRecode>><<field1>> |
| <<field2>> |
| <<field3>> |
| <<field4>> |
| <<NextRecode>><<field1>> |
| <<field2>> |
| <<field3>> |
| <<field4>> |
| <<NextRecode>><<field1>> |
| <<field2>> |
| <<field3>> |
| <<field4>> |
[/tt]

Then use Format > Columns to snake the one column into four.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NIA2]

The only problem is that the table is part of a larger layout on the page so I can't really transpose it. Is there no other way?

 

[NIA2]

Also, even though there's only 4 columns on the one page (that's part of a larger layout), I'm trying to save time so that the first 4 records will come into the first page and then the rest of the 150 records will automatically read into subsequent pages and the layout on the first page will automatically repeat. I don't know if this is possible?

 

[SkipVought]

I'm out of ideas.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NIA2]

Okay - it may not be possible.

 

[macropod]

Hi gwh,

Instead of trying to place the records into the table directly, try assigning them to bookmarks instead, via SET fields (one for each record). You can then insert cross-references to the bookmarks in whatever order you like.

Cheers

[MS MVP - Word]

 

[NIA2]

Thanks for the input macropod,

I'm not really sure how the Set Bookmark field works when doing a data merge. I've just tried to set up the merge again (I'm using Word 2004 on a mac). In the create section I select "form letters", then I click the "get data" button and browse for my excel file.

In the Word field section, there are a whole lot of fields I can drag and drop into the document, so I can drag a Set Bookmark field into the document and it brings up a dialogue box where I can type in the name of the bookmark (you say to do this one for each record). So in the first column where I would normally place the merge fields, I've set a bookmark in each row - one for each field. But I'm not sure what to do next with regard to inserting cross-references to the bookmarks and how to get the other 3 columns (on this first) and all the other columns in the subsequent pages populated.

Can you explain a bit further?

 

[macropod]

Hi gwh,

I don't think you'll get what you need via the standard mailmerge fields insertion options.

Suppose you've got your basic mailmerge document set up with the 16 fields you need for the table:
«field1», «field2», «field3», «field4», «nextrecord»,
«field1», «field2», «field3», «field4», «nextrecord»,
«field1», «field2», «field3», «field4», «nextrecord»,
«field1», «field2», «field3», «field4»

Do the following for each of the fields:
. Select the field and press Ctrl-F9 (I think the Mac equivalent is Command-F9). This wraps another field around the mergefield (eg {«field1»})
. Edit the field code to create the SET code and the corresponding bookmark (eg {SET CellA1 «field1»}). When you're done, your field codes should look something like -
{SET CellA1 «field1»}, {SET CellA2 «field1»}, {SET CellA3 «field1»}, {SET CellA4 «field1»}, «nextrecord»,
{SET CellB1 «field1»}, {SET CellB2 «field1»}, {SET CellB3 «field1»}, {SET CellB4 «field1»}, «nextrecord»,
{SET CellC1 «field1»}, {SET CellC2 «field1»}, {SET CellC3 «field1»}, {SET CellC4 «field1»}, «nextrecord»,
{SET CellD1 «field1»}, {SET CellD2 «field1»}, {SET CellD3 «field1»}, {SET CellD4 «field1»}
. In your table, you can now insert REF fields to cross-reference the bookmarks created via the SET fields:
{REF CellA1} | (REF CellB1} | (REF CellC1} | (REF CellD1}
{REF CellA2} | (REF CellB2} | (REF CellC2} | (REF CellD2}
{REF CellA3} | (REF CellB3} | (REF CellC3} | (REF CellD3}
{REF CellA4} | (REF CellB4} | (REF CellC4} | (REF CellD4}
Again you can create the REF fields via Ctrl-F9/Command-F9.

I assume you've already got the code worked out for creating the next page.

Cheers

[MS MVP - Word]

 

[NIA2]

Thanks for all the info macropod

Do I need to call each of the bookmarks CellA1, CellA2 etc as you have it? If so, I don't know how this would save time, and how would the other pages get populated? Would I have to create another 30 odd pages with the table on it and all the field codes to get it to work?

Am I right in saying that I'm not going to get the automation that a regular mail merge would provide and that it's just going to take this extra work if I want to merge the data this way?

 

[macropod]

Hi gwh,

You can call the bookmarks whatever you like - but you need 16 of them. I used the CellA1 style to make it clear how the bookmarks relate to the table references.

As I said in my previous post, I assumed you'd already got the code worked out for creating the next page. It seems not. You should be able to do the lot with a single page of code.

Your mailmerge template needs a field coded to test whether the current record is a multiple of 4 and, if so insert a page break and start over. The field coding could be something like:

{IF{=MOD({MERGEREC},4)=0 "{QUOTE 12}«nextrecord»" ""}

The whole of the contents of the page, including your table and all the other field coding would be inserted twice - once before the second of each pair of double quotes.

You mentioned having only 150 records. Since that means your record count is not a multiple of 4, you'll need to add some logic to supress any errors this might produce while still outputting the last page. Unfortunately, I din't have access to my own PC to take this further at the moment.

HTH

[MS MVP - Word]

 

[macropod]

Hi gwh,

On reflection, I'm not sure the solution in my last post would work - I suspect Word's catalogue merge won't force the field to loop. Accordingly, you'll need to set up your template with the field coding described in my post of 23 June, then either:
1. use the mailmerge wizard to start your mailmerge at the required record# for each of your 38 pages; or
2. add a «nextrecord» field following the last SET field and a page break at the end of the page, then copy the first page (including the page break) and paste in 37 copies, deleting the last «nextrecord» field from the last page.

Cheers

[MS MVP - Word]

 

[NIA2]

Thanks macropod - appreciate all your help.