Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1198
  • Last Modified:

Using VBA to help with data merge from excel to word

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
gwh2
Asked:
gwh2
1 Solution
 
ms_sanjayCommented:
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
 
gwh2Author Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now