How do I generate a formatted Word report from multi-row/column Excel spreadsheet

Question

I'm trying to auto-build a formatted Word report from an Excel template used by multiple teams. For example, if i have the following Excel structure:

......A.... |.....B.... |....C...
1 Name | Height | Weight
2 Jason | 74 | 170
3 Greg | 70 | 160
4 Sam | 71 | 200

and I want to pull out that data and format into a Word file with the following format:

2.1 Jason
Height: 74
Weigh: 170

2.2 Greg
Height: 70
Weight: 160

2.3 Sam
Height: 71
Weight: 200

Is there a quick way to do that with VBA and be able to iterate through as many rows as may exist in any particular Excel file? (could vary from a few to many hundreds) The real excel file contains about a dozen columns where for each record (row) the data needs to be pulled out and formatted using standard template (font size/color, indent, alignment, etc...) but i'd love to just get the extract to work and I can play with the formatting later.

For reference, I've tried to research known solutions, but most are centered on named bookmarks and relatively static content vs. interating through a dynamic number of rows and parsing the same data for each.

This is very doable... but have you made any attempts to try this with actual code? If so, please post what you've tried and where you are stuck. We can help you much better that way. — Scott Holtzman, Oct 22 '12 at 20:03
Full disclosure, i'm trying to piece this together from what is available out there already, but nothing is really tailored for what i'm trying to do so it's largely not applicable. Also, i'm a total newbie when it comes to this stuff but i'm really hoping to get my feet wet with this implementation. Here's what i'm using for "inspiration"...[link](http://www.ozgrid.com/forum/showthread.php?t=157163&page=2&p=573018#post573018) but it's really a different use case than what i'm looking for as it's not iterating through a range but rather iterating through an array of bookmarks. @ScottHoltzman — user1766409, Oct 22 '12 at 20:10
You can do that with mailmerge quite easily. Once you do it manually it will show you the path for automation. — iDevlop, Oct 22 '12 at 20:10
@iDevlop So i've run into multiple mentions of using mailmerge, but i'm under the impression (perhaps falsely) that mailmerge will create multiple word files...i'm going from one excel (with multiple rows/records) to one word report. If this still applies let me know and i'll chase it straight away. — user1766409, Oct 22 '12 at 20:13
Using the mail merge wizard, you can tell it to create new documents for each entry OR to put them all in the same document. Mail merge is the way I'd choose to accomplish this. — Marc, Oct 22 '12 at 20:33

score 1 · Answer 1 · answered Oct 22 '12 at 20:40

In case you do end up using VBA, you can use the below code starting from a word document. Make sure to have the Reference for Microsoft Excel X.X Object Library checked in under Tools > References in the VBE.

Just so you know, the part where it puts the strings into Word could probably be written better. Word is my weakest of all MS Office products in terms of knowledge.

Sub XLtoWord()

Dim xlApp As Excel.Application
'Set xlApp = CreateObject("Excel.Application")
Set xlApp = GetObject(, "Excel.Application") '-> assumes XL is open, if not use CreateObject

Dim wkb As Excel.Workbook
Set wkb = xlApp.Workbooks("Book5.xlsm") '-> assumes xl is open, if not use .Workbooks.Open(filename)

Dim wks As Excel.Worksheet
Set wks = wkb.Sheets(1) '-> assumes data is in sheet 1

With wks

    Dim lngRow As Long
    lngRow = .Range("A" & .Rows.Count).End(xlUp).Row

    Dim cel As Excel.Range
    Dim i As Integer

    i = 1

    For Each cel In .Range("A2:A" & lngRow) 'assumes data is filled from top left cell of A1 including headers

        strLabel = "2." & i & " " & cel.Text
        strHeight = "Height " & cel.Offset(, 1).Text
        strWeight = "Weight " & cel.Offset(, 2).Text

        Dim myDoc As Word.Document
        Set myDoc = ThisDocument

        myDoc.Range.InsertParagraphAfter
        myDoc.Range.InsertAfter strLabel & Chr(11) & strHeight & Chr(11) & strWeight

        i = i + 1

    Next

End With


End Sub

How do I generate a formatted Word report from multi-row/column Excel spreadsheet

1 Answers1