MS Word

[pedders]

I have a program that creates a report in MS Word 2010 from a database.
For each entry in the database it formats the data into a table in the word document.
However the size of the table can vary if one entry has extraordinarily large field (some 'Memo' fields) it can break a table and split it across 2 pages.

I want at the top of each page a common title (I realise this may be better using header/footers), i then want to start adding tables until one is split, if this happens insert a page break, another title and carry on until the end.

I have tried:

        Dim oWord As Word.Application
        Dim oDoc As Word.Document
        Dim oPara1 As Word.Paragraph
        Dim Pos As Double
        Dim oRng As Word.Range
        oWord = CreateObject("Word.Application")
        oWord.Visible = True
        oDoc = oWord.Documents.Add
        oDoc.PageSetup.LeftMargin = oWord.Application.CentimetersToPoints(1.5)
        oDoc.PageSetup.RightMargin = oWord.Application.CentimetersToPoints(1.5)
        oDoc.PageSetup.TopMargin = oWord.Application.CentimetersToPoints(1.5)
        oDoc.PageSetup.BottomMargin = oWord.Application.CentimetersToPoints(1.5)
        oPara1 = oDoc.Content.Paragraphs.Add
        oPara1.Range.Text = "Page Title"
        oPara1.Range.Font.Bold = True
        oPara1.Range.Font.Underline = True
        oPara1.Range.Font.Size = "16"
        oPara1.Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
        oPara1.Format.SpaceAfter = 6

        Pos = oWord.CentimetersToPoints(23)
        oDoc.Bookmarks.Item("\endofdoc").Range.InsertParagraphAfter()
        Do
            oRng = oDoc.Bookmarks.Item("\endofdoc").Range
            oRng.ParagraphFormat.SpaceAfter = 6
            Dim oTable As Word.Table
            oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, 7, 4)
            oTable.Range.ParagraphFormat.SpaceAfter = 6
            oTable.Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft

            '----- ADD TABLE DATA -----

            oRng = oDoc.Bookmarks.Item("\endofdoc").Range
            oTable.Rows.AllowBreakAcrossPages = False
            ' oPara1.Format.SpaceAfter = 6
            'oRng.InsertParagraphAfter()
        Loop While Pos >= oRng.Information(Word.WdInformation.wdVerticalPositionRelativeToPage)
        oRng.Collapse(Word.WdCollapseDirection.wdCollapseEnd)
        oRng.InsertBreak(Word.WdBreakType.wdPageBreak)
        oRng.Collapse(Word.WdCollapseDirection.wdCollapseEnd)
        oRng.InsertAfter("Page Title")
        oRng.Font.Bold = True
        oRng.Font.Underline = True
        oRng.Font.Size = "16"
        oRng.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
        oRng.ParagraphFormat.SpaceAfter = 6

The do loop sort of works if the table falls after a set position but they may never do that as the overall table height could vary (although I must point out that I envisage a single table should never get to a position where it is bigger than 1 page!)

[TnTinMN]

Here is a Word VBA macro that will split a table whenever the page number changes. It appears that you are fluent in Word Interop so it should not be too hard for you make the needed changes to convert it to VB.Net and add your Title Insert code.

Sub SplitTable(t As Word.Table)
    Dim r As Row
    Dim CurPage As Integer
    Dim LastPage As Integer
    t.Rows(1).Select
    LastPage = Selection.Information(wdActiveEndPageNumber)
        For Each r In t.Rows
        r.Select ' Selected it so its the activerange
        CurPage = Selection.Information(wdActiveEndPageNumber)
        If CurPage <> LastPage Then
            t.Split (r.Index)
            r.Select ' Selected it so its the activerange
            Dim NewTable As Table
            Set NewTable = Selection.Tables(1)
            Call SplitTable(NewTable)
            Exit For
        End If
     Next r
End Sub

[pedders]

Thanks for that, I must admit though I am NOT as fluent as my previous post makes out, it is stuff cobbled from code examples on the internet!

I shall have a look and see if I can bodge it in see if it'll work...

[TnTinMN]

It should be easy to convert. Just delete the "Set" keywords and adust some namespace references.

I just did not feel like setting up a VB project to test it. I develop this stuff in VBA first because it gives instant results and is easy to prototype and I have yet to find anything that does not easily convert to .Net interop. Although, it can sometimes be a challenge to find what namespace VBA constants are hidden under on the .Net side.

If you have any problems, just ask.

[pedders]

Sorry to be a pain but not being fluent im struggling:

                Dim r As Word.Row
                Dim CurPage As Integer
                Dim LastPage As Integer
                oTable.Rows(1).Select()
                LastPage = Selection.Information(wdActiveEndPageNumber)
                For Each r In oTable.Rows
                    r.Select() ' Selected it so its the activerange
                    CurPage = Selection.Information(wdActiveEndPageNumber)
                    If CurPage <> LastPage Then
                        oTable.Split(r.Index)
                        r.Select() ' Selected it so its the activerange
                        Dim NewTable As Table
                        NewTable = Selection.Tables(1)
                        Call SplitTable(NewTable)
                        Exit For
                    End If
                Next r

Selection.Information(wdActiveEndPageNumber) = wdActiveEndPageNumber not declared error

changing to:

LastPage = Selection.Information(WdInformation.wdActiveEndPageNumber) = Selection.Information reference to non shared member error

Also

NewTable = Selection.Tables(1) = Selection.Information reference to non shared member error

Call SplitTable(NewTable) = not declared error

[TnTinMN]

Rats! I forgot to tell you how to get the active Selection. Oh well, see if this makes any sense to you.

Imports Microsoft.Office.Interop

Public Class Form1
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      PlayWithWord()
   End Sub

   Private Sub PlayWithWord()
      Dim app As New Word.Application
      app.Visible = True
      Dim doc As Word.Document = app.Documents.Open("D:\My Documents\Splittable.docx")
      ' remember that office indices start at 1 not 0
      SplitTable(doc.Tables(1)) 'Split the first table up if it needs splitting
      doc.Close(False)
      doc = Nothing
      app.Quit()
      app = Nothing
      GC.Collect()
   End Sub

   Sub SplitTable(ByVal t As Word.Table)
       Dim r As Word.Row
       Dim CurPage As Integer
       Dim LastPage As Integer
       t.Rows(1).Select() ' remember that office indices start at 1 not 0

       Dim myapp As Word.Application = t.Tables.Application
       LastPage = CInt(myapp.Selection.Information(Word.WdInformation.wdActiveEndPageNumber))

       For Each r In t.Rows
          r.Select() ' Selected it so its the activerange
          CurPage = CInt(myapp.Selection.Information(Word.WdInformation.wdActiveEndPageNumber))
          If CurPage <> LastPage Then
             t.Split(r.Index)
             r.Select() ' Selected it so its the activerange
             Dim NewTable As Word.Table
             NewTable = myapp.Selection.Tables(1)
             Call SplitTable(NewTable)
             Exit For
          End If
       Next r
    End Sub

End Class

[pedders]

I can't seem to get this to work the way I want, I want it to check each table not just the first.

My program creates a document and fills it with, at present 31 tables, then I tried doing a For loop for each table in oDoc.Tables.
I modified the code slightly so it selects the last row of the table first to get the "LastPage" then when it does get to a case where CurPage <> LastPage it tries the t.Split(r.Index) but I get a Bad Parameter error.

[TnTinMN]

Running a for loop on the tables will not work as by splitting then, you are adding to the collection.

In your report generation code, as soon as you are finished populating a table, pass its reference to the split routine. This assumes you are following a start to end generation pattern.

[pedders]

I find that the line at the end of table creation SplitTable(oDoc.Tables(1)) 'Split the first table up if it needs splitting will check the table at the top of the page every time, if i use SplitTable(oDoc.Tables(rec+1)) 'Split the first table up if it needs splitting so it in theory should check the table number i get "The requested member of the collection does not exist."

My entire code for creating the tables:

Dim oWord As Word.Application
    Dim oDoc As Word.Document
    Dim oTable As Word.Table
    Dim oPara1 As Word.Paragraph
    Dim Pos As Double
    Dim oRng As Word.Range

    Public Sub CounterRep()
        oWord = CreateObject("Word.Application")
        oWord.Visible = True
        oDoc = oWord.Documents.Add
        oDoc.PageSetup.LeftMargin = oWord.Application.CentimetersToPoints(1.5)
        oDoc.PageSetup.RightMargin = oWord.Application.CentimetersToPoints(1.5)
        oDoc.PageSetup.TopMargin = oWord.Application.CentimetersToPoints(2)
        oDoc.PageSetup.BottomMargin = oWord.Application.CentimetersToPoints(1.5)

        With oDoc.Sections(1).Headers(Word.WdHeaderFooterIndex.wdHeaderFooterPrimary).Range
            .Font.Bold = True
            .Font.Underline = True
            .Font.Size = "16"
            .Text = "Counter Report"
            .ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
        End With

        Dim rec As Integer
        For rec = 0 To sn.Count - 1
            oRng = oDoc.Bookmarks.Item("\endofdoc").Range
            oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, 7, 4)
            oTable.Rows.AllowBreakAcrossPages = False
            oTable.Range.ParagraphFormat.SpaceAfter = 6
            oTable.Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft
            oTable.Range.Font.Size = "11"
            oTable.Range.Font.Underline = False
            oTable.Range.Font.Bold = False

            oTable.Columns(1).Width = oWord.Application.CentimetersToPoints(4.5)
            oTable.Columns(2).Width = oWord.Application.CentimetersToPoints(5)
            oTable.Columns(3).Width = oWord.Application.CentimetersToPoints(3.5)
            oTable.Columns(4).Width = oWord.Application.CentimetersToPoints(5)

            oTable.Cell(1, 1).Range.Text = "Counter Serial Number:"
            oTable.Cell(1, 2).Range.Text = sn(rec)
            oTable.Cell(1, 1).Range.Font.Bold = True
            oTable.Cell(1, 3).Range.Text = "Phone Number:"
            oTable.Cell(1, 4).Range.Text = pn(rec)
            oTable.Cell(1, 3).Range.Font.Bold = True
            oTable.Cell(2, 1).Range.Text = "Manufacturer:"
            oTable.Cell(2, 2).Range.Text = man(rec)
            oTable.Cell(2, 1).Range.Font.Bold = True
            oTable.Cell(2, 3).Range.Text = "Counter Model:"
            oTable.Cell(2, 4).Range.Text = mo(rec)
            oTable.Cell(2, 3).Range.Font.Bold = True
            oTable.Cell(3, 1).Range.Text = "Site Ref:"
            oTable.Cell(3, 2).Range.Text = ref(rec)
            oTable.Cell(3, 1).Range.Font.Bold = True
            oTable.Cell(3, 3).Range.Text = "District:"
            oTable.Cell(3, 4).Range.Text = di(rec)
            oTable.Cell(3, 3).Range.Font.Bold = True
            oTable.Cell(4, 1).Range.Text = "AZ Reference:"
            oTable.Cell(4, 2).Range.Text = az(rec)
            oTable.Cell(4, 1).Range.Font.Bold = True
            oTable.Cell(4, 3).Range.Text = "OSGR"
            oTable.Cell(4, 4).Range.Text = east(rec) & "," & north(rec)
            oTable.Cell(4, 3).Range.Font.Bold = True
            oTable.Cell(5, 1).Range.Text = "Site Description:"
            oTable.Cell(5, 2).Range.Text = desc(rec)
            oTable.Cell(5, 1).Range.Font.Bold = True
            oTable.Cell(6, 1).Range.Text = "Counter Notes:"
            oTable.Cell(6, 2).Range.Text = no(rec)
            oTable.Cell(6, 1).Range.Font.Bold = True
            oTable.Cell(9, 1).Range.Text = "Site Notes:"
            oTable.Cell(9, 2).Range.Text = snote(rec)
            oTable.Cell(9, 1).Range.Font.Bold = True

            oTable.Cell(5, 2).Merge(MergeTo:=oTable.Cell(5, 4))
            oTable.Cell(6, 2).Merge(MergeTo:=oTable.Cell(6, 4))
            oTable.Cell(7, 2).Merge(MergeTo:=oTable.Cell(7, 4))

            oTable.Rows.AllowBreakAcrossPages = False
            oPara1 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)
            oRng = oDoc.Bookmarks.Item("\endofdoc").Range

            SplitTable(oDoc.Tables(rec+1)) 'Split the first table up if it needs splitting 
            If rec = sn.Count - 1 Then Exit For
            rec = rec + 1
        Next
    End Sub

I have some arrays with the values I want in each table, so it does a For loop so i get all the data in the tables.

Am I just being really thick?

[TnTinMN]

Well I, thought that what I left you to figure out was not a trival as I thought. The darn thing looses the row reference when adding a formated paragraph for you Title. It took a bit of experimenting, but I think if you follow the pattern shown below it should work for you. At least the test case worked.

Imports Microsoft.Office.Interop

Public Class Form1
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      PlayWithWord()
   End Sub

   Private Sub PlayWithWord()
      Dim app As New Word.Application
      app.Visible = True

      Dim doc As Word.Document = app.Documents.Add 'app.Documents.Open("D:\My Documents\Splittable.docx")
      Dim view As Word.View = app.ActiveWindow.View
      With view.Zoom
        .PageColumns = 2
        .PageRows = 1
      End With
      view = Nothing
      Dim table As Word.Table

      doc.Bookmarks.Item("\endofdoc").Select()
      app.Selection.Text = "Flintstones. Meet the Flintstones. They're the modern stone age family.  From the town of Bedrock, They're a page right out of history."

      doc.Bookmarks.Item("\endofdoc").Select()
      table = doc.Tables.Add(app.Selection.Range, 70, 1)
      Stop
      Dim Title As String = "Top of Page Title"

      SplitTable(table, Title)

      Stop

      doc.Bookmarks.Item("\endofdoc").Select()
      app.Selection.Text = "fred was here" & vbNewLine
      Stop

      doc.Bookmarks.Item("\endofdoc").Select()
      app.Selection.Range.Select()
      Stop

      table = doc.Tables.Add(app.Selection.Range, 50, 1)
      Stop
      SplitTable(table, Title)
      Stop

      doc.Bookmarks.Item("\endofdoc").Select()
      app.Selection.Text = "end of doc"

      Stop

      doc.Close(False)
      doc = Nothing
      app.Quit()
      app = Nothing
      GC.Collect()
   End Sub

   Sub SplitTable(ByVal t As Word.Table, ByVal Title As String)
       Dim r As Word.Row
       Dim CurPage As Integer
       Dim LastPage As Integer
       Const wdCharacter As Int32 = 1

       t.Rows(1).Select() ' remember that office indices start at 1 not 0

       Dim myapp As Word.Application = t.Tables.Application
       LastPage = CInt(myapp.Selection.Information(Word.WdInformation.wdActiveEndPageNumber))

       For Each r In t.Rows
          r.Select() ' Selected it so its the activerange
          CurPage = CInt(myapp.Selection.Information(Word.WdInformation.wdActiveEndPageNumber))
          If CurPage <> LastPage Then

             t.Split(r.Index)

             ' unfortunately the row reference is destroyed by adding a paragraph
             ' so will need to get the new table refernce first then move back out of the table

             r.Select() ' Selected it so its the activerange
             Dim NewTable As Word.Table
             NewTable = myapp.Selection.Tables(1)
             myapp.Selection.MoveLeft(wdCharacter, 2)

             ' wrap the Title with a PageBreakBefore format
             ' to prevent it from jumping to the previous page
             Dim para As Word.Paragraph = myapp.Selection.Paragraphs.Add(myapp.Selection.Range)
             para.Format.PageBreakBefore = -1
             para.Format.SpaceAfter = 0
             myapp.Selection.Range.Text = Title

             Call SplitTable(NewTable, Title)
             Exit For
          End If
       Next r
    End Sub

End Class

[pedders]

Thanks for all the help, but i'm beginning to think I've bitten off more than I can chew, something that seemed so simple is proving to be a bit of a headache.
I can't get it to work for my particular example.
Your code above works but I can't get it to work how I want it to for my example.
Below is my Code condensed down to just creating the tables without data, it creates 4 tables on page on, on the 5th the last row jumps to page 2, the splitting splits off the last row onto the new page, rather than moving the whole of the 5th table to the top of the second page.

 Public Sub CounterRep()
        oWord = CreateObject("Word.Application")
        oWord.Visible = True

        oDoc = oWord.Documents.Add
        Dim view As Word.View = oWord.ActiveWindow.View
        With view.Zoom
            .PageColumns = 2
            .PageRows = 1
        End With
        view = Nothing
        oDoc.PageSetup.LeftMargin = oWord.Application.CentimetersToPoints(1.5)
        oDoc.PageSetup.RightMargin = oWord.Application.CentimetersToPoints(1.5)
        oDoc.PageSetup.TopMargin = oWord.Application.CentimetersToPoints(2)
        oDoc.PageSetup.BottomMargin = oWord.Application.CentimetersToPoints(1.5)

        With oDoc.Sections(1).Headers(Word.WdHeaderFooterIndex.wdHeaderFooterPrimary).Range
            .Font.Bold = True
            .Font.Underline = True
            .Font.Size = "16"
            .Text = "Counter Report"
            .ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter
        End With

        'Keep inserting text. When you get to 7 inches from top of the
        'document, insert a hard page break.
        Dim rec As Integer
        For rec = 0 To sn.Count - 1
            oDoc.Bookmarks.Item("\endofdoc").Select()
            oTable = oDoc.Tables.Add(oWord.Selection.Range, 7, 4)
            oTable.Rows.AllowBreakAcrossPages = False
            oTable.Range.ParagraphFormat.SpaceAfter = 6
            oTable.Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft
            oTable.Range.Font.Size = "11"
            oTable.Range.Font.Underline = False
            oTable.Range.Font.Bold = False

            oTable.Columns(1).Width = oWord.Application.CentimetersToPoints(4.5)
            oTable.Columns(2).Width = oWord.Application.CentimetersToPoints(5)
            oTable.Columns(3).Width = oWord.Application.CentimetersToPoints(3.5)
            oTable.Columns(4).Width = oWord.Application.CentimetersToPoints(5)

            oPara1 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)
            SplitTable(oTable)
            If rec = sn.Count - 1 Then Exit For
            rec = rec + 1
        Next
End Sub

Sub SplitTable(ByVal t As Word.Table)
        Dim r As Word.Row
        Dim CurPage As Integer
        Dim LastPage As Integer
        Const wdCharacter As Int32 = 1
        t.Rows(1).Select() ' remember that office indices start at 1 not 0
        Dim myapp As Word.Application = t.Tables.Application
        LastPage = CInt(myapp.Selection.Information(Word.WdInformation.wdActiveEndPageNumber))
        't.Rows(1).Select() ' remember that office indices start at 1 not 0
        ' myapp  = t.Tables.Application
        For Each r In t.Rows
            r.Select() ' Selected it so its the activerange
            CurPage = CInt(myapp.Selection.Information(Word.WdInformation.wdActiveEndPageNumber))
            If CurPage <> LastPage Then
                t.Split(r.Index)
                ' unfortunately the row reference is destroyed by adding a paragraph
                ' so will need to get the new table refernce first then move back out of the table
                r.Select() ' Selected it so its the activerange
                Dim NewTable As Word.Table
                NewTable = myapp.Selection.Tables(1)
                myapp.Selection.MoveLeft(wdCharacter, 2)
                ' wrap the Title with a PageBreakBefore format
                ' to prevent it from jumping to the previous page
                Dim para As Word.Paragraph = myapp.Selection.Paragraphs.Add(myapp.Selection.Range)
                para.Format.PageBreakBefore = -1
                para.Format.SpaceAfter = 0
                'myapp.Selection.Range.Text = Title
                Call SplitTable(NewTable)
                Exit For
            End If
        Next r
    End Sub

I removed/commented out the "Title" references as I don't require one at the moment.

I think I have realised with the above I'm trying to split a table, rather than PREVENT a table from splitting or at least if it does split across pages move the whole table...

[pedders]

I thinkI've got it do what I wanted... (I think!)

        Dim CurPage As Integer
        Dim LastPage As Integer
        t.Rows(7).Select() ' remember that office indices start at 1 not 0
        Dim myapp As Word.Application = t.Tables.Application
        LastPage = CInt(myapp.Selection.Information(Word.WdInformation.wdActiveEndPageNumber))
        t.Rows(1).Select() ' remember that office indices start at 1 not 0
        myapp = t.Tables.Application
        CurPage = CInt(myapp.Selection.Information(Word.WdInformation.wdActiveEndPageNumber))
        If CurPage <> LastPage Then
            myapp.Selection.InsertBreak()
        End If

So if first and last rows of table are on different pages then page break at the beginning!
All goes to pot if 1 table is bigger than 1 complete page, but I don't envisage that happening! (I hope!)