PDA

View Full Version : Build Word Table From Excel Spreadsheet



vodkha
04-01-2005, 09:54 AM
I'm trying to build multiply tables in word with data from an excel spread sheet.
for instance the first row of data in my spreadsheet will be in one table and the second row in another table. I started a code that creates a table in word but i don't know how to populate it with the data.

Private Sub MakeTable()
Dim objWd As Word.Application
Dim objDoc As Word.Document
Dim objRange As Word.Range
Set objWd = CreateObject("Word.Application")
objWd.Visible = True
Set objDoc = objWd.Documents.add
Set objRange = objDoc.Range(0, 0)
objDoc.Tables.add Range:=objRange, _
NumRows:=15, _
NumColumns:=2, _
DefaultTableBehavior:=wdWord9TableBehavior, _
AutoFitBehavior:=wdAutoFitContent

With objDoc
.Tables(1).Columns(1).Width = 150
.Tables(1).Columns(2).Width = 300
.Tables(1).Columns(1).Shading.BackgroundPatternColor = wdColorGray10
.Tables(1).Rows.Height = AutoFit
.Tables(1).Rows.Alignment = wdAlignRowCenter
.Tables(1).Cell(1, 1).Range = Sheet1.Cells(A, 1).Value
End With
End Sub

Killian
04-01-2005, 12:33 PM
Hiya vodkha, welcome to VBAX :hi:

You can loop through the corresponding rows and columns like this'.Tables(1).Cell(1, 1).Range = Sheet1.Cells(1, 1).Value
For r = 1 To 15
For c = 1 To 2
.Tables(1).Cell(r, c).Range = Sheet1.Cells(r, c).Value
Next
Next assuming yourXL data starts in A1. Otherwise you'l have to define your range of data. It would be worth it though because you could set the table creation and row and column counters in the loop with varibles initialized with that range's .Rows.Count & .Columns.Count. That way it would work with different data ranges. (you might havr to let the Word table width auto fit tho)

vodkha
04-01-2005, 01:42 PM
i need to dim r & c as integers correct and my data starts with A2

Killian
04-01-2005, 03:44 PM
correct. An then you'll need to add one to the row index for the excel rangeFor r = 1 To 15
For c = 1 To 2
.Tables(1).Cell(r, c).Range = Sheet1.Cells(r + 1, c).Value
Next
Next