Excel

Create formatted Word table from Excel data range

Ease of Use

Easy

Version tested with

2002, 2003 

Submitted by:

Killian

Description:

This short and simple routine copies a range of cells and pastes it into a new Word file from a template with a formatted table style. 

Discussion:

With some minor tweaking, this routine provides a quick and easy way of outputting Excel data into formatted Word tables for inclusion in your reports. It could provide the framework (with some extra coding and extension of the styles in the Word template) for a cumstomized data report generator. 

Code:

instructions for use 

			
Option Explicit 
 
Sub Data2Word() 
     
     'Remember: this code requires a referece to the Word object model
     
     'dimension some local variables
    Dim rng As Range 'our source range
    Dim wdApp As New Word.Application 'a new instance of Word
    Dim wdDoc As Word.Document 'our new Word document
    Dim t As Word.Range 'the new table in Word as a range
    Dim myWordFile As String 'path to Word template
     
     
     'initialize the Word template path
     'here, it's set to be in the same directory as our source workbook
    myWordFile = ThisWorkbook.Path & "\DocWithTableStyle.dot" 
     
     'get the range of the contiguous data from Cell A1
    Set rng = Range("A1").CurrentRegion 
     'you can do some pre-formatting with the range here
    rng.HorizontalAlignment = xlCenter 'center align the data
    rng.Copy 'copy the range
     
     'open a new word document from the template
    Set wdDoc = wdApp.Documents.Add(myWordFile) 
     
    Set t = wdDoc.Content 'set the range in Word
    t.Paste 'paste in the table
    With t 'working with the table range
        .Style = "GreenBar" 'set the style created for the table
         'we can use the range object to do some more formatting
         'here, I'm matching the table with using the Excel range's properties
        .Tables(1).Columns.SetWidth (rng.Width / rng.Columns.Count), wdAdjustSameWidth 
    End With 
     
     'until now the Word app has been a background process
    wdApp.Visible = True 
     'we could use the Word app object to finish off
     'you may also want to things like generate a filename and save the file
    wdApp.Activate 
     
End Sub

How to use:

  1. First, prepare a Word template that has a table style set how you want it. The example attached has one set with a Heading row and odd row formatting (producing a table where the alternate rows are shaded light green).
  2. Save the Word file as a template. Close Word and open Excel. Add a few rows and columns of data (from cell A1) and save in the same folder as the Word template.
  3. Open the Visual Basic Editor and go to Tools>References
  4. You will need to select the reference to the Microsoft Word (version number) Object model
  5. Add a new module and paste in the code below.
  6. Save the workbook and run the routine!
 

Test the code:

  1. Tools | Macro | Macros...
  2. Select Data2Word and press Run.
 

Sample File:

Data2WordTable.zip 13.84KB 

Approved by mdmackillop


This entry has been viewed 329 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express