Word Table

[BobBob10]

Hi there I need to create a word table on the fly.

What I am doing is connecting to a sql database and returning a recordset. This recordset has 3 columns labelled Age, Name, Sex. However, the number of rows produced can vary e.g from 1 to 20. Therefore, I need to create this table and then add rows in each time I go to the next record.

I am not used to word macros, so any ideas would be greatly appreciated.

Cheers.

Bob.

 

[ACH381]

Hi Bob

In Word's VBA editor do a search in help for "ConvertToTable". Your sql recordset should be a delimited text string with the columns delimited by commas, or tabs and the rows delimited by carriage returns. After you've run the query and have the recordset in memory place the recordset text into the Word document where you want it. Then run the ConvertToTable method with the proper arguments. This will convert the text to a table on the fly. You can then add headings and format the table with scripts.

I'm using it in an application and instead of trying to append rows to the table, I just delete the table and rerun the creation script to create a new table with the new information from the sql table query. I'm actually not pulling the query from sql; I'm gathering data from other tables in my document and creating the text string, but once you have any text in a delimited string this will work. The example in the book I was using was actually for a recordset string.

Here's my script: I have some global variables declared elsewhere, so you may need to watch for that.


Sub CreateFC_ContactTable()
On Error GoTo ErrHnd
SUFalse
SetWindowToNormal
Dim CTL As String
Dim X, T As Integer
Dim objTable As Word.Table
Dim aCell As Cell
Dim TblNms(6) As String
TblNms(0) = "CONTACT NUMBERS (In Case of Emergency):"
TblNms(1) = "FIRE DEPARTMENTS:"
TblNms(2) = "LAW ENFORCEMENT AGENCIES:"
TblNms(3) = "EMERGENCY MEDICAL SERVICES:"
TblNms(4) = "MISCELLANEOUS AGENCIES / SERVICES:"
TblNms(5) = "EMERGENCY CONTACT NUMBERS:"

TC = 0
TC = ActiveDocument.Tables.Count
TNo = 0
UnlockForm
'Cleanup Get rid of the old table so a new one can be created.
TNo = TC
While TNo > 0
If InStr(ActiveDocument.Tables(TNo).Rows(1).Range.Text, TblNms(0)) <> 0 Then
ActiveDocument.Tables(TNo).Delete
Else: TNo = TNo - 1
End If
Wend
'Recount tables after deleting
TC = ActiveDocument.Tables.Count
'Gather the information for the new table.
'Fill a variable named CTL with carriage return delimited rows of tab delimited data.
For T = 1 To 5
TNo = TC
While TNo > 0
If InStr(ActiveDocument.Tables(TNo).Rows(1).Range.Text, TblNms(T)) <> 0 Then
Set TN = ActiveDocument.Tables(TNo)
TRN = TN.Rows.Count

Select Case True
Case TblNms(T) <> "EMERGENCY CONTACT NUMBERS:"
For X = 3 To TRN
If TN.Rows(X).Range.FormFields(1).Result <> "" Then
CTL = CTL & TblNms(T) & Chr(9) & _
TN.Rows(X).Range.FormFields(1).Result & Chr(9) & _
TN.Rows(X).Range.FormFields(2).Result & Chr(13)
Else:
Exit For
End If
Next

Case TblNms(T) = "EMERGENCY CONTACT NUMBERS:"
For X = 3 To TRN
If TN.Rows(X).Range.FormFields(1).Result <> "" Then
CTL = CTL & UCase(TN.Rows(X).Range.FormFields(2).Result) & Chr(9) & _
TN.Rows(X).Range.FormFields(1).Result & Chr(9) & _
"Work-" & TN.Rows(X).Range.FormFields(4).Result & _
" Home-" & TN.Rows(X).Range.FormFields(5).Result & _
" Cell-" & TN.Rows(X).Range.FormFields(6).Result & Chr(13)
Else:
Exit For
End If
Next
End Select
TNo = 0
Else: TNo = TNo - 1
End If
Wend
Next

'Create the table
ActiveDocument.FormFields("FC_ContactTable").Range.FormFields(1).Select
Selection.MoveDown Unit:=wdLine, Count:=1
With Selection
.InsertAfter CTL
Set objTable = .ConvertToTable(Separator:=wdSeparateByTabs, AutoFit:=True, defaulttablebehavior:=wdWord9TableBehavior)
With objTable
.Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Range.Cells.VerticalAlignment = wdCellAlignVerticalCenter
.Columns(3).PreferredWidth = InchesToPoints(1.8)
.Columns(2).PreferredWidth = InchesToPoints(2.8)
.Columns(1).PreferredWidth = InchesToPoints(3.1)
.Rows.Add(.Rows(1)).HeadingFormat = True
.Rows.Add(.Rows(1)).HeadingFormat = True
.Rows(1).Cells.Merge
With .Cell(1, 1).Range
.Font.Bold = True
.Text = "CONTACT NUMBERS (In Case of Emergency):"
End With
.Rows(2).Range.Font.Bold = True
.Cell(2, 1).Range.Text = "AGENCY NAME / TITLE"
.Cell(2, 2).Range.Text = "NAME"
.Cell(2, 3).Range.Text = "PHONE NUMBER"
End With
End With
ProtectForm
SetWindowToPrint
SUTrue
ActiveDocument.FormFields("FC_ContactTable").Select
Exit Sub

ErrHnd:
ProtectForm
SetWindowToPrint
SUTrue
ActiveDocument.FormFields("FC_ContactTable").Select
End Sub

If you need more help let me know and I'll send you the exact syntax from the book I found this in.

Allan

 

[fumei]

Not quite gathering the requirements here.

Are you trying to make ONE table? I can't figure out if you want to CHANGE the table, or make a new one depending on the number of rows?

If you can get the number of rows needed, just use that as a variable.

ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=[b][i]number of rows[/i][/b], NumColumns:= _
        3, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed

Are you are asking about having an existing table, but adding one row at at a time?

Gerry

 

[BobBob10]

Hi,

I'm connecting a database and then going to make the table from scratch, depending on the number of rows from the rocerdset returned.

 

[fumei]

Again, then, if you can get the number of rows that WILL be returned, use the code above to make the table with that many rows.

However, you have not really answered the question. I will try and rephrase.

Are you making a table with ONE row, then adding rows as you work through the recordset? Or do you know the number of rows when you are FIRST making the table?



Gerry