Solved

Using VBA to help with data merge from excel to word

Posted on 2008-06-22
2
1,183 Views
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.

0
Comment
Question by:gwh2
2 Comments
 
LVL 1

Accepted Solution

by:
ms_sanjay earned 500 total points
ID: 21862633
Hi,
  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")
Else
        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
    Sheets("Sheet1").Select
    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)
    TheRange.Select
    TheRange.Copy
    Set wdRng = moDocument.Range
    With wdRng.Find
           If .Execute(lsValue) Then
              'Paste the copied text in the word document
               wdRng.PasteSpecial
           End If

Let me know how it goes.

Thanks
Sanjay Samuel.
0
 
LVL 1

Author Closing Comment

by:gwh2
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.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now