Formatting Word Table 3

[Swi]

I posted this in the VB 5 & 6 forum but thought that I may get better results here. Here is my code:

    With rs
        .CursorLocation = adUseServer
        .Open "SELECT [STATE], COUNT(*) AS [QUANTITY] FROM [" & FileName & "]" & _
        "GROUP BY [STATE]", conn, adOpenForwardOnly, adLockReadOnly, adCmdText
        If .BOF And .EOF Then
        Else
            ' Create an instance of Word
            Set oWord = New Word.Application 'CreateObject("Word.Application")
            ' Show Word to the user
            oWord.Visible = False
    
            ' Add a new, blank document
            Set oDoc = oWord.Documents.Add
            ' Get the current document's range object
            Set oRange = oDoc.Range
    
            ' Use GetString to return the recordset as a string
            sTemp = rs.GetString(adClipString, -1, vbTab)
      
            ' Insert a heading on the string
            sTemp = "State" & vbTab & "Quantity" & vbCrLf & sTemp
    
            ' Insert the data into the Word document
            oRange.Text = sTemp
    
            ' Convert the text to a table and format the table
            oRange.ConvertToTable vbTab, , , , wdTableFormatColorful2
        End If
        .Close
    End With

What I would like to do is have the count fill across and not down in the table so I can fit more of the count on one page. For example:

STATE COUNT STATE COUNT STATE COUNT
AL 50 AK 50 AR 50
AZ 50 PA 50 OH 50

Also, could someone shed so light on how to add multiple tables with the method used above? Thanks.

Swi

 

[xwb]

Why not create a table then fill it in on the fly. You will have very much better control over it and don't have to keep everything in memory and convert it to a table.

 

[Swi]

Can you provide a quick example? Thanks.

Swi

 

[white605]

I might be out of line here, but this is the code i use to add and format a table to the bottom of my document and put data into the cells - Language is VFP but the logic would be the same in vb.

*move to end of document
oRange.MoveEnd(wdstory)
oRange.Collapse(wdCollapseEnd)
oRange.InsertAfter(cr+cr)
oRange.insertAfter("Alantic Coast Life Payment Options")
oRange.InsertAfter(cr+cr)
oRange.Collapse(wdCollapseEnd)

*add a table with two rows
oTable=lOWord.activeDocument.Tables.Add(oRange,5,7)

WITH oTable
	*set up borders and shading
	*first remove all borders
	.Borders.InsideLineStyle=.f.
	.Borders.OutsideLineStyle= .f.
	
	*shade first row for headers
	.Rows[1].Shading.Texture=100
	
	*put heading text in  and set alignment
	.Cell[1,1].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[1,1].Range.Insertafter("Years")
	
	.Cell[1,2].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[1,2].Range.Insertafter("Single")
	
	.Cell[1,3].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[1,3].Range.Insertafter("Annual")

	.Cell[1,4].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[1,4].Range.Insertafter("Semi")

	.Cell[1,5].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[1,5].Range.Insertafter("QTR")

	.Cell[1,6].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[1,6].Range.Insertafter("Monthly")

	.Cell[1,7].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[1,7].Range.Insertafter("Draft")

	
	
*format data cells
FOR n=2 TO 5
	.Cell[n,1].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[n,2].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[n,3].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[n,4].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[n,5].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[n,6].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
	.Cell[n,7].Range.ParagraphFormat.Alignment=wdAlignParagraphRight
endfor
	

	
*add data and format
	SELECT thisone

	COUNT TO nRecCount
	GOTO top

	FOR nRow =1 TO nRecCount
		WITH .Rows[NRow+1]
			.Cells[1].Range.InsertAfter( thisone.years )
			.cells[2].Range.InsertAfter ( IIF("*"$STR(thisone.single_amt),[ ],thisone.single_amt) )
			.cells[3].Range.InsertAfter ( IIF("*"$STR(thisone.annual_amt),[ ],thisone.annual_amt) )
			.cells[4].Range.InsertAfter ( IIF("*"$STR(thisone.semi_amt),[ ],thisone.semi_amt) )
			.cells[5].Range.InsertAfter ( IIF("*"$STR(thisone.qtr_amt),[ ],thisone.qtr_amt) )
			.cells[6].Range.InsertAfter ( IIF("*"$STR(thisone.mon_amt),[ ],thisone.mon_amt) )
			.cells[7].Range.InsertAfter ( IIF("*"$STR(thisone.mdraft_amt),[ ],thisone.mdraft_amt) )
			
		ENDWITH 
	*add a new row
	.Rows.add()
	
	*skip to next record
	SKIP
	ENDFOR
	
	
			
endwith

 

[Swi]

Thanks for the information. I will try to give it a shot this weekend. Thanks.

Swi

 

[Swi]

Thanks for the start:

Private Sub Command1_Click()
Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim oTable As Word.Table
Dim oRange As Word.Range

'Start Word and open the document template.
Set oWord = CreateObject("Word.Application")
oWord.Visible = True
Set oDoc = oWord.Documents.Add
Set oRange = oDoc.Range(0, 0)

' move to end of document
oRange.MoveEnd (wdstory)
oRange.Collapse (wdCollapseEnd)
oRange.InsertAfter ("State Verification Counts")
oRange.Font.Name = "Times New Roman"
oRange.Font.Size = 16
oRange.Font.Bold = True
oRange.Collapse (wdCollapseEnd)

'add a table with two rows
Set oTable = oWord.ActiveDocument.Tables.Add(oRange, 5, 6)

With oTable
    'set up borders and shading
    'first remove all borders
    .Borders.InsideLineStyle = False
    .Borders.OutsideLineStyle = False
    
    'shade first row for headers
    .Rows(1).Shading.Texture = 100
    
    'put heading text in and set alignment
    .Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    .Cell(1, 1).Range.Font.Bold = wdToggle
    .Cell(1, 1).Range.InsertAfter ("State")

    .Cell(1, 2).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    .Cell(1, 2).Range.Font.Bold = wdToggle
    .Cell(1, 2).Range.InsertAfter ("Count")
    
    .Cell(1, 3).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    .Cell(1, 3).Range.Font.Bold = wdToggle
    .Cell(1, 3).Range.InsertAfter ("State")

    .Cell(1, 4).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    .Cell(1, 4).Range.Font.Bold = wdToggle
    .Cell(1, 4).Range.InsertAfter ("Count")

    .Cell(1, 5).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    .Cell(1, 5).Range.Font.Bold = wdToggle
    .Cell(1, 5).Range.InsertAfter ("State")

    .Cell(1, 6).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    .Cell(1, 6).Range.Font.Bold = wdToggle
    .Cell(1, 6).Range.InsertAfter ("Count")

' format data cells
For n = 2 To 5
    .Cell(n, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Cell(n, 2).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Cell(n, 3).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Cell(n, 4).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Cell(n, 5).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Cell(n, 6).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
Next
    

    
'add data and format
    'SELECT thisone

    'COUNT TO nRecCount
    'GoTo Top

    'For NRow = 1 To nRecCount
        'With .Rows(NRow + 1)
        '    .cells(1).Range.InsertAfter (thisone.years)
        '    .cells(2).Range.InsertAfter ( IIF("*"$STR(thisone.single_amt),( ),thisone.single_amt) )
        '    .cells(3).Range.InsertAfter ( IIF("*"$STR(thisone.annual_amt),( ),thisone.annual_amt) )
        '    .cells(4).Range.InsertAfter ( IIF("*"$STR(thisone.semi_amt),( ),thisone.semi_amt) )
        '    .cells(5).Range.InsertAfter ( IIF("*"$STR(thisone.qtr_amt),( ),thisone.qtr_amt) )
        '    .cells(6).Range.InsertAfter ( IIF("*"$STR(thisone.mon_amt),( ),thisone.mon_amt) )
        '    .cells(7).Range.InsertAfter ( IIF("*"$STR(thisone.mdraft_amt),( ),thisone.mdraft_amt) )
        'End With
    'add a new row
    '.Rows.add()
    
    'skip to next record
    'Skip
    'Next
    oDoc.SaveAs "C:\Test.doc"
    oDoc.Close
    Set oRange = Nothing
    Set oTable = Nothing
    Set oDoc = Nothing
    oWord.Quit
    Set oWord = Nothing
    MsgBox "Done!", vbInformation
End With
End Sub

How can I insert multiple tables in the document?

Swi

 

[xwb]

You need to select a new range and

Set oTable = oWord.ActiveDocument.Tables.Add(oRange, 5, 6)

The range could simply be the end of the document. Do you really need multiple tables or will setting repeat row header on every page do?

 

[Swi]

I really need multiple tables as I will be counting different data fields and want to display them in seperate tables.

Swi

 

[fumei]

Just some comments.

1. Some of this is not needed. It is a new blank document, so there IS nothing there. There is no need to move to the End. It is already at the end. There is no need to collapse, as it is already just a point.

Set oDoc = oWord.Documents.Add
Set oRange = oDoc.Range(0, 0)

' move to end of document
oRange.MoveEnd (wdstory)
oRange.Collapse (wdCollapseEnd)

2. All those separate instructions for each cell are not needed. Make a Row object and use it. You are making each cell individually AlignCenter...why not just make the Row that way? You are making each cell individually Bold. Why not make the Row that way?

Dim oRow As Word.Row
Dim oTable As Word.Table

[COLOR=red]' BTW:  this does not make TWO rows[/color red]
'add a table with two rows
Set oTable = ActiveDocument.Tables.Add(Range:=orange, _
        numrows:=5, numcolumns:=6)
[COLOR=red], but in any case you can make a Row object
' of the first row, then USE it[/color red]
Set oRow = oTable.Rows(1)
With oTable
    'set up borders and shading
    'first remove all borders
    .Borders.InsideLineStyle = False
    .Borders.OutsideLineStyle = False
End With
With oRow
    .Shading.Texture = 100
    .Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
    .Range.Font.Bold = True
    .Cells(1).Range.Text = "State"
    .Cells(2).Range.Text = "Count"
    .Cells(3).Range.Text = "State"
    .Cells(4).Range.Text = "Count"
    .Cells(5).Range.Text = "State"
    .Cells(6).Range.Text = "Count"
End With

Further, the code:

For n = 2 To 5
    .Cell(n, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Cell(n, 2).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Cell(n, 3).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Cell(n, 4).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Cell(n, 5).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
    .Cell(n, 6).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
Next

is not needed at all, as those are already the default - Left aligned.

Even if they were not (but they are), rather than formatting each cell individually - total of 24 instructions - you could again just use the Row object (for only a total of 4 instructions).

Set oRow = Nothing

For n = 2 To 5
    Set oRow = oTable.Rows(n)
    oRow.Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
Next

faq219-2884

Gerry
My paintings and sculpture

 

[Swi]

Thank you for the modifications. Can anyone give me a pointer about how to add multiple tables in one word document though?

Swi

 

[SkipVought]

Thisdocument.Tables.Add

method. refer to HELP.


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[fumei]

Can anyone give me a pointer about how to add multiple tables in one word document though?

I don't understand the question.

You added a table....so, ummmm, add another one, then....another one....then another one.

Other than that, you will have to actually explicitly state what it is you want to do. Add them where? Your code makes a blank new document, so, ummm, it does not have many places to add "multiple" tables. Except one....then another...then another.

BUT, put something between them. Otherwise Word will mush them together.

faq219-2884

Gerry
My paintings and sculpture

 

[Swi]

That is my problem. Word smushed them together. I guess I just need to add a line between them. Thanks.

Swi

 

[white605]

Gerry,
Thanks for pointing out the eaiser row object for building tables. My next automation will definately be better, as I read here a "lot" - The more I read the more I learn!
wjwjr

 

[fumei]

drum roll.....

My usual rant. Regulars, you may leave any time you wish.

Styles. Styles. Styles.

I have a style called Spacer. ALL my tables have a Spacer paragraph after them. It is simply a wee font size. 1 pt. This can be adjusted for whatever you want. I also have a Style called "TableHolder" which is simply a centering style, as I want my tables centered.

Sub MultipleTables()
Dim j As Long
For j = 1 To 5
   With Selection
      .TypeParagraph
      .Style = "TableHolder"
      ActiveDocument.Tables.Add Range:=Selection.Range, _
          Numrows:=j, Numcolumns:=3
      .EndKey Unit:=wdStory
      .Style = "Spacer"
      .TypeParagraph
   End With
Next
End Sub

This would make five tables (of increasing number of rows), separated by my Spacer style. The tables would be very close...but not touching. So they would not be mushed.

NOTE: this works well if you are putting multiple tables in a row. But what it you want to insert a table somewhere, and you do not want to affect any following text?

Yes, you can "add a line", but if you use styles, YOU control what that actually means.

Sub InsertTable()
With Selection
   .Collapse Direction:=wdCollapseEnd
   .TypeParagraph
   .Style = "TableHolder"
   ActiveDocument.Tables.Add Range:=Selection.Range, _
       Numrows:=3, Numcolumns:=3
[COLOR=red]   ' go to end of table[/color red]
   .EndOf unit:=wdTable, Extend:=wdMove
[COLOR=red]   ' move just outside of table[/color red]
   .Move unit:=wdCharacter, Count:=2
[COLOR=red]   ' add the Spacer[/color red]
   .Style = "Spacer"
   .TypeParagraph
End With
End Sub

This inserts a table at the Selection point (collapsing it first just in case), using the TableHolder style, then moves just outside the end of the table, and inserts a paragraph with the Spacer style.

faq219-2884

Gerry
My paintings and sculpture