I'm new to the forums and VBA and I found exactly what I needed through a knowledge base article:
Unfortunately, I can't link to it but here is the code--
[VBA]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 [/VBA]
I wanted to first say thank you you guys for all of your help and expertise!
I have a few questions, if you don't mind helping me out:
1. How do I get this VBA code to run against multiple sheets in my workbook?
2. I downloaded the example, but I'm not sure how to style the tables, is there something in the code? The example has two documents, but when I open them they are blank. Just to be clear I don't understand the "greenbar" style and where that is coming from. I'd like to create my own style
Thank you in advance for your help as I didn't go to school for this!
Reply With Quote