Solved: Create table in Word from Excel spreadsheet

[Solrac3030]

I'm trying to create a table in a Word template with data coming from a Excel file. The table has only the title row and a blank empty row below the titles. The Excel spreadsheet will change everytime and the number of records will not be the same, the Column headings will always be the same and the number of columns will always be the same. In the Word document I cannot define the size of the table as to how many rows because it is not known. Nor can I use bookmarks or textform fields since I will not know how many records there will be in the Excel Spreadsheet. I need to find a way to create the table dynamically as the code moves through each record and puts the data from each column into each cell in the table and after each record insert a new row into the Word table. The code would look at the Excel file and determine the number of records run the loop and for each record put the contents of column 1 into row 2 column 1 of the table and so on, then add a new row to the Word table.

Need help on getting the code started or at least a starting point.

Thanks.

[geekgirlau]

This sounds like a mailmerge to me

[Solrac3030]

You would think so but a mail merge would create a document with many pages and I wjust want a document with a list of the data in it. I can use the copy feature and copy the data to the document but then the user would have to apply some formatting to the table and I don't want the user to do anything other than print or save the document.

[Solrac3030]

If I can figure out how to set the widths of the columns after the data is copied that would solve my problem as VBA would do the necessary formatting of the table. I tried to set the column width but was not successful. I also need to set the allignment property for column 1 as it comes out right justified as it is a number, need to make it left justified.

[geekgirlau]

Actually you are incorrect - it is possible to set up a mailmerge that does not put each record on a new page.

[Solrac3030]

Would you let me know how to do that?

[Solrac3030]

Ended up using a loop to copy each record from the spreadsheet to the word doument.

[geekgirlau]

Have a look at the NEXT field - this should give you what you're after.

[TonyJollans]

A "Directory" Merge is what you need.

[Dave]

I'm sure a merge as outlined would be better. Here's some untrialled stuff that might help. HTH. Dave

Sub XLToWordTable()
Dim ObjWord As Object, Rng As Range
Dim wrdDoc As Object, Ocell As Variant, TC As Variant
Dim Lastrow As Integer, Lastcol As Integer, Cnt As Integer
'adds XL cell contents to Word .dot table(1)from XL
'no Word reference required

On Error GoTo Erfix
'open existing word .dot file ie. "D:\tabletest.dot"
'*** "D:\tabletest.dot" MUST exist ie. change address to suit
Set ObjWord = CreateObject("Word.Application")
Set wrdDoc = ObjWord.Documents.Open(Filename:="D:\tabletest.dot")

'set XL range to suit
'determine table sixe from Xl range (used range in this eg.)
Lastrow = Sheets("Sheet1").UsedRange.Rows.Count
Lastcol = Sheets("Sheet1").UsedRange.Columns.Count

'vba set XL range
With Sheets("Sheet1")
Set Rng = .Range(.Cells(1, 1), .Cells(Lastrow, Lastcol))
End With

'add cells to table(1) in this example (ie change table to suit)
' -1 re. existing title row and blamk row
With wrdDoc
.Tables(1).Add ObjWord.Selection.Range, _
numrows:=Lastrow - 1, Numcolumns:=Lastcol - 1
End With
'insert XL cell.value to table location
'table(1) in this example (ie change table to suit)
Cnt = 2
For Each Ocell In Rng
Set TC = ObjWord.ActiveDocument.Tables(1).Range.Cells(Cnt)
TC.Range.InsertAfter Ocell.Value
Cnt = Cnt + 1
Next Ocell

'fit table and add adjustments/margins
With ObjWord.ActiveDocument.Tables(1)
.Columns.AutoFit
'.Rows.SetLeftIndent LeftIndent:=-57.6, RulerStyle:=False
'.Columns(3).SetWidth ColumnWidth:=153.3, RulerStyle:=False
'.Columns(4).SetWidth ColumnWidth:=144, RulerStyle:=False
End With

wrdDoc.SaveAs "D:\TEST.DOC" 'change file name to suit
wrdDoc.Close savechanges:=False
Set wrdDoc = Nothing
ObjWord.Quit
Set ObjWord = Nothing
MsgBox "Finished"
Exit Sub

Erfix:
On Error GoTo 0
MsgBox "error"
Set wrdDoc = Nothing
ObjWord.Quit
Set ObjWord = Nothing
End Sub

[macropod]

Hi solrac3030,

As suggested by others, you can probably use Word's Catalogue/Directory Mailmerge facility for this (the terminology depends on the Word version). To see how, check out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://lounge.windowssecrets.com/index.php?showtopic=731107 (mhtml:{2603C627-B617-484B-B3E3-521BCFB808E8}mid://00000040/!x-usc:http://lounge.windowssecrets.com/index.php?showtopic=731107)
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip (mhtml:{2603C627-B617-484B-B3E3-521BCFB808E8}mid://00000040/!x-usc:http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip)
Do read the tutorial before trying to use the mailmerge document included with it.