How To Add An Unknown Number of Paragraphs & Tables When Automating Word
I am trying to change a working Word automation subroutine to allow a variable number of paragraphs and tables and have run into some difficulty. I had hoped to be able to define the range, paragraph and table objects as arrays and then define them as part of the Word document as I looped through the data. The problem is that some of the items are members of System.Array so that approach will not work. I would like suggestions on how to address a variable number of paragraphs and tables when automating Word. My several E-E searches have been unsuccessful.
Public Sub WordPrintTWSampleSize(ByVal gNbrCategories As Short)
Try
Dim oWord As Object
Dim oDoc As Object
Dim oPara1 As Object
Dim oPara2 As Object
Dim oRng() As Object
Dim DateTime As Date
Dim iCatInProcess As Short = 0
Dim oPar() As Object
Dim oTable(iCatInProcess)() As Object
ReDim oPar(gNbrCategories - 1)
ReDim oTable(iCatInProcess)(gNbrCategories - 1)
ReDim oRng(gNbrCategories - 1)
DateTime = Now
'Start Word and open the document template.
oWord = CreateObject("Word.Application")
oDoc = oWord.Documents.Add
oPara1 = oDoc.Content.Paragraphs.Add
oPara2 = oDoc.Content.Paragraphs.Add
oWord.Visible = True
oDoc.PageSetup.Orientation = 1 ' 1 is landscape, 0 is portrait
oDoc.PageSetup.TopMargin = 1.0#
oDoc.PageSetup.BottomMargin = 0.75
'Insert a paragraph at the beginning of the document.
oPara1 = oDoc.Content.Paragraphs.Add
oPara1.Range.Font.Size = 8
oPara1.Range.Text = Space(283) & DateTime & vbCrLf
oPara1.Range.Font.Size = 12
oPara1.Range.ParagraphFormat.Alignment = 1
oPara1.Range.Text = "Two Way Stratification and Sample Size Report" & vbCr
If gParentCompanyName.Length > 0 Then
oPara1.Range.Text = Trim(gParentCompanyName) & vbCr & vbCr
End If
Dim R, c As Short
For iCatInProcess = 0 To gNbrCategories - 1
oPar(iCatInProcess) = oDoc.Content.Paragraphs.Add
oPar(iCatInProcess).Range.Text = Trim(TWAuditInfo(iCatInProcess).CompanyName) & vbCr
oPar(iCatInProcess).Range.Bold = False
oPar(iCatInProcess).Format.SpaceAfter = 12
oPar(iCatInProcess).Range.InsertParagraphAfter()
' Insert a TWAuditInfo(icatinprocess).nbrstrata row by 8 column table here and make the first row
' bold. Fill the other cells with data
oRng(iCatInProcess) = oDoc.bookmarks.item("\endofdoc").range
oTable(iCatInProcess) = oDoc.Tables.Add(oRng(iCatInProcess), TWAuditInfo(iCatInProcess).NbrStrata + 2, 7, DefaultTableBehavior:=1, AutoFitBehavior:=1)
oTable(iCatInProcess)(iCatInProcess).Range.ParagraphFormat.SpaceAfter = 6
oTable(iCatInProcess)(iCatInProcess).Range.Font.Size = 11
oTable(iCatInProcess).Rows(1).Range.Font.Bold = True
R = 1
c = 1
oTable(iCatInProcess).Columns(c).Width = oWord.InchesToPoints(0.75)
oTable(iCatInProcess).Cell(R, c).Range.Text = "Stratum"
oTable(iCatInProcess).Cell(R, c).Range.ParagraphFormat.Alignment = 1
c = 2
oTable(iCatInProcess).Columns(c).Width = oWord.InchesToPoints(1)
oTable(iCatInProcess).Cell(R, c).Range.Text = "Lower Boundary"
oTable(iCatInProcess).Cell(R, c).Range.ParagraphFormat.Alignment = 1
c = 3
oTable(iCatInProcess).Columns(c).Width = oWord.InchesToPoints(1)
oTable(iCatInProcess).Cell(R, c).Range.Text = "Upper Boundary"
oTable(iCatInProcess).Cell(R, c).Range.ParagraphFormat.Alignment = 1
c = 4
oTable(iCatInProcess).Columns(c).Width = oWord.InchesToPoints(1)
oTable(iCatInProcess).Cell(R, c).Range.Text = "Stratum Size"
oTable(iCatInProcess).Cell(R, c).Range.ParagraphFormat.Alignment = 1
c = 5
oTable(iCatInProcess).Columns(c).Width = oWord.InchesToPoints(1.5)
oTable(iCatInProcess).Cell(R, c).Range.Text = "Stratum Total"
oTable(iCatInProcess).Cell(R, c).Range.ParagraphFormat.Alignment = 1
c = 6
oTable(iCatInProcess).Columns(c).Width = oWord.InchesToPoints(1)
oTable(iCatInProcess).Cell(R, c).Range.Text = "Standard Deviation"
oTable(iCatInProcess).Cell(R, c).Range.ParagraphFormat.Alignment = 1
c = 7
oTable(iCatInProcess).Columns(c).Width = oWord.InchesToPoints(1)
oTable(iCatInProcess).Cell(R, c).Range.Text = "Sample Size"
oTable(iCatInProcess).Cell(R, c).Range.ParagraphFormat.Alignment = 1
For R = 0 To TWAuditInfo(iCatInProcess).NbrStrata - 1
oTable(iCatInProcess).Rows(R + 2).Range.Font.Bold = False
oTable(iCatInProcess).Cell(R + 2, 1).Range.Text = (R + 1).ToString
oTable(iCatInProcess).Cell(R + 2, 1).Range.ParagraphFormat.Alignment = 1
oTable(iCatInProcess).Cell(R + 2, 2).Range.Text = TWStratSumm(iCatInProcess).Lower(R).ToString("$#,###,##0.00")
oTable(iCatInProcess).Cell(R + 2, 2).Range.ParagraphFormat.Alignment = 2
oTable(iCatInProcess).Cell(R + 2, 3).Range.Text = TWStratSumm(iCatInProcess).Upper(R).ToString("$#,###,##0.00")
oTable(iCatInProcess).Cell(R + 2, 3).Range.ParagraphFormat.Alignment = 2
oTable(iCatInProcess).Cell(R + 2, 4).Range.Text = TWAuditInfo(iCatInProcess).Nh(R).ToString("##,##0")
oTable(iCatInProcess).Cell(R + 2, 4).Range.ParagraphFormat.Alignment = 2
oTable(iCatInProcess).Cell(R + 2, 5).Range.Text = TWAuditInfo(iCatInProcess).Th(R).ToString("$##,##0.00")
oTable(iCatInProcess).Cell(R + 2, 5).Range.ParagraphFormat.Alignment = 2
oTable(iCatInProcess).Cell(R + 2, 6).Range.Text = TWAuditInfo(iCatInProcess).Sh(R).ToString("#,##0.00")
oTable(iCatInProcess).Cell(R + 2, 6).Range.ParagraphFormat.Alignment = 2
oTable(iCatInProcess).Cell(R + 2, 7).Range.Text = TWAuditInfo(iCatInProcess).NHh(R).ToString("###,##0")
oTable(iCatInProcess).Cell(R + 2, 7).Range.ParagraphFormat.Alignment = 2
Next R
R = TWAuditInfo(iCatInProcess).NbrStrata + 2
oTable(iCatInProcess).Cell(R, 3).Range.Text = "Totals"
oTable(iCatInProcess).Cell(R, 3).Range.ParagraphFormat.Alignment = 2
oTable(iCatInProcess).Cell(R, 4).Range.Text = TWAuditInfo(iCatInProcess).NN.ToString("###,##0")
oTable(iCatInProcess).Cell(R, 4).Range.ParagraphFormat.Alignment = 2
oTable(iCatInProcess).Cell(R, 5).Range.Text = TWAuditInfo(iCatInProcess).TotalInvoices.ToString("$###,###,##0.00")
oTable(iCatInProcess).Cell(R, 5).Range.ParagraphFormat.Alignment = 2
oTable(iCatInProcess).Cell(R, 7).Range.Text = TWAuditInfo(iCatInProcess).TotalSamsize.ToString("###,##0")
oTable(iCatInProcess).Cell(R, 7).Range.ParagraphFormat.Alignment = 2
Next iCatInProcess
Catch ex As Exception
End Try
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
vb jonas & ianmair329:
I figured it out this afternoon. Dimensioning the paragraphs in an array of objects works fine. This does not work for tables because it is not necessary. Tables are a collection in a Word document so all that is necessary is to dimension one table as an object and then use that in the For . . . Next loop. It works very well.
The paragraphs are only titles so I am going to try to put them in a title row of the table and do away with the paragraphs in the For . . . Next loops. I am still trying to figure out how to select only the first row and then merge the cells. If you can help with that, I would appreciate it.
I am going to close this question and split the points evenly. Thanks for your suggestions.
I figured it out this afternoon. Dimensioning the paragraphs in an array of objects works fine. This does not work for tables because it is not necessary. Tables are a collection in a Word document so all that is necessary is to dimension one table as an object and then use that in the For . . . Next loop. It works very well.
The paragraphs are only titles so I am going to try to put them in a title row of the table and do away with the paragraphs in the For . . . Next loops. I am still trying to figure out how to select only the first row and then merge the cells. If you can help with that, I would appreciate it.
I am going to close this question and split the points evenly. Thanks for your suggestions.
ASKER
Got the merge, too:
Dim oCell as Word.Cell
oCell = oTable.cell(1, 1)
oTable.cell(1, 7).merge(oCell)
Dim oCell as Word.Cell
oCell = oTable.cell(1, 1)
oTable.cell(1, 7).merge(oCell)
Happy to help - and glad you sorted that merge! Good luck with the rest of your project
ASKER
Thanks for your suggestions. I have to use late binding since I don't know which version of Office any particular user is using. Otherwise, unlike VB6, I would need to compile a different version for each different version of Office.
I thought of an array list but could not figure out how to implement it. The paragraphs for the individual categories are the same except for the data itself, so that may work fine. The tables all have the same number of columns but have different numbers of rows. Each row is filled from a structure which is an item in an array list. The structures have all the statistical information for a single audit. I will investigate using a template for the column structure provided I can handle the number of rows.
The big problem is that one time the document could have just two tables and another time it may be ten. I think I can put the associated paragraph into the table so all I have to work out is adding a variable number of tables. Is there a way to create the table in a separate subroutine and then insert it into the primary document? As you can tell, Word automation is essentially foreign territory for me.