What is the best way to write data to a table

[Zephid15]

I have written a form that has the price and description of a product. now when the user selects one of the products and presses the continue button i want the program to take that data and construct a nice table on a word document. have a good amount of it written although i am having issues moving from cell to cell within the table. is there a better way do do this?


heres the code i have so far. i have not written all of the move commands in but, when i went to test what i have so far it has been giving me an error message at the "Selection.MoveDown Unit:=wdCell" parts.

Thanks for your help.
Dave

ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=3, NumColumns:= _
3, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
wdAutoFitFixed
With Selection.Tables(1)
If .Style <> "Table Grid" Then
.Style = "Table Grid"
End If
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = True
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = True
End With

Selection.Font.Size = 10
Selection.Font.Bold = wdToggle
Selection.TypeText Text:="1)"
Selection.MoveRight Unit:=wdCell
Selection.Font.Bold = wdToggle
Selection.TypeText Text:="Name"
Selection.MoveRight Unit:=wdCell
Selection.Font.Bold = wdToggle
Selection.TypeText Text:="Number"
Selection.MoveDown Unit:=wdCell
Selection.MoveLeft Unit:=wdCell
Selection.MoveLeft Unit:=wdCell
Selection.Font.Size = 8
Selection.TypeText Text:="DESC"
Selection.MoveRight Unit:=wdCell
Selection.InlineShapes.AddPicture FileName:= _
"C:\Documents and Settings\dl134880\Desktop\clip_image002.jpg", _
LinkToFile:=False, SaveWithDocument:=True
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
Selection.MoveDown Unit:=wdCell
Selection.MoveLeft Unit:=wdCell
Selection.MoveLeft Unit:=wdCell
Selection.Cells.Split NumRows:=1, NumColumns:=5, MergeBeforeSplit:=False
Selection.MoveRight Unit:=wdCell
Selection.MoveRight Unit:=wdCell
Selection.MoveRight Unit:=wdCell
Selection.MoveRight Unit:=wdCell
Selection.MoveRight Unit:=wdCell
Selection.MoveRight Unit:=wdCell
Selection.Cells.Split NumRows:=1, NumColumns:=5, MergeBeforeSplit:=False
Selection.MoveLeft Unit:=wdCell
Selection.MoveLeft Unit:=wdCell
Selection.MoveLeft Unit:=wdCell
Selection.MoveLeft Unit:=wdCell
Selection.MoveLeft Unit:=wdCell
Selection.MoveLeft Unit:=wdCell
Selection.TypeText Text:="QTY"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="qty"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="UNIT"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="unit"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="TOTAL"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="total"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="DIS"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="dis"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="NET"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:="net"

Selection.MoveLeft Unit:=wdCharacter, Count:=12, Extend:=wdExtend
ActiveWindow.ActivePane.VerticalPercentScrolled = 25
Selection.Shading.Texture = wdTextureNone
Selection.Shading.ForegroundPatternColor = wdColorAutomatic
Selection.Shading.BackgroundPatternColor = wdColorGray45
Selection.Font.Color = wdColorBlack
Selection.Font.Color = wdColorWhite
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.MoveUp Unit:=wdLine, Count:=2
Selection.MoveDown Unit:=wdLine, Count:=4
Selection.MoveUp Unit:=wdLine, Count:=2
Selection.MoveRight Unit:=wdCharacter, Count:=13, Extend:=wdExtend
Selection.Font.Size = 8
Selection.Cells.DistributeWidth
ActiveWindow.ActivePane.VerticalPercentScrolled = 25
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveUp Unit:=wdLine, Count:=2, Extend:=wdExtend
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Cells.DistributeHeight
End If


Unload Me
End Sub

[shasur]

Why can't you use the Range.Text. This doesnot require moving between cells

Here is how you can work on:

Sub Write_TextIn_Tables()

Dim MyTab As Table
Dim sCellText
Dim bTextFound As Boolean


Set MyTab = ActiveDocument.Tables(1)

For i = 1 To MyTab.Columns.Count
For j = 1 To MyTab.Rows.Count
MyTab.Cell(j, i).Range.Text = "Sample Text Column " & i & " Row " & j
Next j
Next i

End Sub

[Zephid15]

The data on the page is going to be in a different format then the data in the form. On the form the user will see qty, name, description, price, discount, and total. on the quote i want it to be formated like this:

5610



How can i write data to a table like that. now i have sucessfuly writen each cell at a time using code just using:

Selection.TypeText Text:="textbox1.value"

but moving to other cells has become an issue.

thanks for the help

[lucas]

Why not use a template and have your form open on document new....then use bookmarks to place the texbox values in each cell where you want them...you can also reference the table cells directly.

[lucas]

From another thread....how to create a table and add text to the cells:
Option Explicit

Sub AddTable()
Dim Tbl As Table
Set Tbl = ActiveDocument.Tables.Add(Range:=Selection.Range, _
NumRows:=4, NumColumns:=2)
With Tbl
.Range.Font.Bold = True
.Cell(1, 1).Range.Text = "AAA"
.Cell(1, 2).Range.Text = "111"
End With
End Sub

[fumei]

The point being is that you can access and write to any cell directly, by row/column. You do NOT need to use Selection, and move it.

Your problem though is that you are splitting cells. Ugh, yuck. Split cells is a real pain with VBA. It can be done though. You need to map out your table and determine precisely the column numbers for any specific row.

The idea of using a template and filling it in is a good one, and the recommended route.

[Zephid15]

I was using the split cells because it allowed the spacing to work out correctly. i also am not using auto formating because it does not allow for the gray bar at the bottom to be gray but the left most cell to remain white (so the "1)" stands out).

I will give the AddTable()

thanks

[Zephid15]

how would i go about formatting that table? How do i adjust the size, shape, color, font, spaceing, allignment?

[lucas]

Format a template with the look you like and use code to fill the cells of the newly cloned document....they will all look the same that way and it's much more efficient.

[Zephid15]

i just trie to do that and it gave me an error message saying that the name was not defined:

Sub AddTable()
Dim Tbl As Table
Set Tbl = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=3, NumColumns:=11)
With Tbl
.Range.Font.Bold = True
.Cell(1, 1).Range.Text = "1"
.Cell(1, 2).Range.Text = txt1a.Value
.Cell(1, 3).Range.Text = txt1b.Value
.Cell(2, 2).Range.Text = txt1c.Value
.Cell(2, 11).Range.Text = "IMAGE"

With Dialogs(wdDialogTableAutoFormat)

.Format = QuickQuote '<-------custom template
.Borders = True
.Shading = True
.Font = True
.Color = True
.HeadingRows = True
.LastRow = True
.FirstColumn = True
.LastColumn = True
.AutoFit = True
.Execute
End With
End With
End Sub

[lucas]

Take a look at this Zeph....just extract it and double click on it...it creates a cloned document of the rfi.dot so you don't have to worry about messing it up. Tinker with it and let me know what you think...It doesn't use tables but you can see how the template will work in your favor.

[fumei]

How does it know what QuickQuote means? Word has no idea what QuickQuote means. It is not defined.

[Zephid15]

i just trie to do that and it gave me an error message saying that the name was not defined:

Sub AddTable()
Dim Tbl As Table
Set Tbl = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=3, NumColumns:=11)
With Tbl
.Range.Font.Bold = True
.Cell(1, 1).Range.Text = "1"
.Cell(1, 2).Range.Text = txt1a.Value
.Cell(1, 3).Range.Text = txt1b.Value
.Cell(2, 2).Range.Text = txt1c.Value
.Cell(2, 11).Range.Text = "IMAGE"

With Dialogs(wdDialogTableAutoFormat)

.Format = QuickQuote '<-------custom template
.Borders = True
.Shading = True
.Font = True
.Color = True
.HeadingRows = True
.LastRow = True
.FirstColumn = True
.LastColumn = True
.AutoFit = True
.Execute
End With
End With
End Sub

[Zephid15]

is there anything extra that i need to add to the name of the template?

[fumei]

1. You need to use a predefined constant, like:
.Format = wdTableFormatClassic1

2. using a dialog to do these kind of actions is very inefficient. You can modify format directly.

[Zephid15]

so i can not use one of the templates that i created?

[Zephid15]

What do you mean modify the format directly? Go to Table->Table autoformat? If that’s what you mean, I don’t want to do that. I do not want any additional steps for the user once they exit the userform.

[fumei]

Does "directly" sound like "autoformat"?

No...I do NOT mean autoformat. Autoformat has those first four letters...."auto".

I mean format directly.
With ActiveDocument.Tables(1)
.Borders.OutsideLineStyle = wdLineStyleDouble
.Style = "MyLastRowTableStyle"
.ApplyStyleLastRow = True
End WithNo dialog. It formats the table DIRECTLY. In the above example, Table 1 is set with a double border, and uses a custom style MyLastRowTableStyle. Theuser sees nothing. The instruction are executed.

Again, .Format will ONLY use the predefined formats.

However you can format the table elements DIRECTLY.With ActiveDocument.Tables(1)
.Borders.OutsideLineStyle = wdLineStyleDouble
.Shading.BackgroundPatternColorIndex = wdDarkBlue
.AllowPageBreaks
.BottomPadding = PixelsToPoints(40, True)
'etc etc whatever whatever
End With
More examples. No dialog to the user. The instructions are executed, the table is formatted. Directly.

You have to be more clear of what you mean by "template".