Using VBA to help with data merge from excel to word

Posted on 2008-06-22
Last Modified: 2013-12-20
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.

Question by:gwh2

Accepted Solution

ms_sanjay earned 500 total points
ID: 21862633
  try the below code.

Dim rAcells As Range
Dim TheRange As Range
Dim lsColumnValue As Integer
Dim lsRowValue As Integer

If TypeName(moWordApp) = "Nothing" Then
        Set moWordApp = CreateObject("Word.Application")
        Set moWordApp = GetObject(, "Word.Application")
End If
Set moDocument = moWordApp.Documents.Open("C:\Temp\XYZ.xls")
Set rAcells = ActiveWorkbook.Sheets("Data_Sheet").UsedRange
    moDocument.ActiveWindow.Visible = True
    lsColumnValue = rLoopCells.Cells(1, 2)
    lsRowValue = rLoopCells.Cells(1, 3)
    Application.CutCopyMode = False
    'Store the chunk of text that needs to be copied
    Set TheRange = rLoopCells.Offset(1, 0).Resize(lsRowValue, lsColumnValue)
    Set wdRng = moDocument.Range
    With wdRng.Find
           If .Execute(lsValue) Then
              'Paste the copied text in the word document
           End If

Let me know how it goes.

Sanjay Samuel.

Author Closing Comment

ID: 31469529
Thanks for taking the time to provide your code. Unfortunately I'm not able to test with it at the moment as my client has changed the brief.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question