Populating tables in Word from Excel pivot tables

[jennc90]

Hi all,

I am looking for some help with finding the fastest and simplest way to populate tables I've created in Microsoft Word using data from pivot tables in Microsoft Excel.

I have a Word document (a template I've created) with 15 different tables. This template must be populated at least 50 times with 50 different sets of data. What I thought would work best would be to structure my pivot tables in such a way to mimic my Word tables, so I could change variables with a filter and copy paste the data, but that seems too laborious considering I have to populate this template so many times. Also, the work involved in doing that may exceed the time I have to work on the project.

My template includes columns for counts, proportions and share. There can be as many as 20 columns. I have, at most, 25 rows. That's a lot of numbers! One of my tables has count, proportion and rank.

So, for example, one table has 15 variables (in the rows). For each variable I need to fill in numbers in 3 columns: Count, proportion and rank.

A few people have suggested using a macro but I'm not quite sure how to go about creating one that will grab the data from the pivots and load them into the Word tables.

I would appreciate any help you could provide.

Thank you!

 

[Macropod]

Populating Word tables with Excel data via a macro is not particularly difficult, even without a Pivot table - all you need to express in the code is the logic for what data go where.

A fairly basic macro to create & populate a document from a template is:

Sub Demo()
'Note: A Reference to the Microsoft Word Object Library
' is required, via Tools>References.
Dim xlWkSht As Worksheet
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Set wdApp = New Word.Application
Dim i As Long
Set xlWkSht = ActiveSheet
With wdApp
  .Visible = True
  .ScreenUpdating = False
  Set wdDoc = .Documents.Add(Template:="Template path & name")
  With wdDoc
    For i = 1 To .Tables.Count
      Select Case i
        Case 1
          With .Tables(i)
            .Cell(1, 1).Range.Text = xlWkSht.Range("A1").Text
            .Cell(1, 2).Range.Text = xlWkSht.Range("B1").Text
            .Cell(2, 1).Range.Text = xlWkSht.Range("A2").Text
            .Cell(2, 2).Range.Text = xlWkSht.Range("B2").Text
          End With
        Case 2
          With .Tables(i)
            .Cell(1, 1).Range.Text = xlWkSht.Range("D1").Text
            .Cell(1, 2).Range.Text = xlWkSht.Range("E1").Text
            .Cell(2, 1).Range.Text = xlWkSht.Range("D2").Text
            .Cell(2, 2).Range.Text = xlWkSht.Range("E2").Text
          End With
      End Select
    Next
  End With
  .ScreenUpdating = True
End With
End Sub

Of course, I don't know any of the logic behind your pivot tables, so I can't replicate that. That said, if you know your requirements well enough, you can conditionally add rows to tables to account for varying amounts of data from one run of the macro to the next. And you can delete tables for which there is no content, if need be.

 

[jennc90]

Thank you very much! I will try that today.