Solved

Using VBA to help with data merge from excel to word

Posted on 2008-06-22
2
1,190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…

734 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