Hi everyone,
I have a problem doing a data merge from an excel spreadsheet to a Word table. I thought the project was going to be simple in that I was going to use Word's mail merge feature to get it working but I've run into problems.
My set up is as follows:
I have an excel spreadsheet with 5 columns and about 150 rows. I also have a one-page Word document with a 5 row, 4 column table (along with other text etc. on the page). I'm essentially looking to have the spreadsheet columns transposed from an excel list arrangement to a columnar arrangement in the Word document. So I need the 5 fields in the spreadsheet (i.e which represent one complete row) to merge 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, but the 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 of the spreadsheet.
So I wanted this 4 column table on the first page of the document to be populated with the first 4 data records (rows) from excel, then I wanted a new page to automatically be inserted and the next 4 records to be merged, etc., etc. until there are enough pages to complete the data merge.
I thought this method would work automatically because 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.
I wondered if someone could comment on my set up and tell me whether I can get this to work either by using an adaptation of Word's mail merge feature or whether my requirements would need the help of VBA. If VBA is needed, can you tell me what I would need to do to set this up, how hard it would be etc.?
Sorry for such a long explanation of the problem but I thought it needed it to make it clear.
Appreciate any advice.
Start Free Trial
