New page in Word

Ok,

My program writes address labels to a Word template from a database. Avery 5160 standard labels come 30 to a page... There will be days where we have more than 30 labels, though...

My question is:

How do I create a new page in the current open template in Word, but use the same bookmarks as page 1?

VB Code:

Private Sub cmdGetLabels_Click()
 
    Dim template As String
    Dim WordApp As Word.Application
    Dim WordDoc As Word.Document
    Dim WordTemp As Word.Document
    Dim docPath As String
    Dim intTimes As Integer
    Dim strLetter As String
    Dim strUser As String
    Dim strStatus As String
    Dim docPageCount As Integer
    
    '--> Get template and document storage path
    template$ = getTemplate
 
    ConnectDatabase
       
    '--> Start MS Word and open template
    Set WordApp = CreateObject("Word.Application")
    WordApp.Visible = False
    'Set WordTemp = WordApp.Documents.Open("C:\Documents and Settings\" & Environ("Username") & "\Application Data\Microsoft\Templates\Normal.dot")
    Set WordDoc = WordApp.Documents.Open(FileName:=template$, Revert:=True)
   
   
intTimes = 1
   
With rsCustomerTable
 
Do Until .EOF
    
    Select Case intTimes
    
        Case "1"
            strLetter = "A"
        Case "2"
            strLetter = "B"
        Case "3"
            strLetter = "C"
        Case "4"
            strLetter = "D"
        Case "5"
            strLetter = "E"
        Case "6"
            strLetter = "F"
        Case "7"
            strLetter = "G"
        Case "8"
            strLetter = "H"
        Case "9"
            strLetter = "I"
        Case "10"
            strLetter = "J"
        Case "11"
            strLetter = "K"
        Case "12"
            strLetter = "L"
        Case "13"
            strLetter = "M"
        Case "14"
            strLetter = "N"
        Case "15"
            strLetter = "O"
        Case "16"
            strLetter = "P"
        Case "17"
            strLetter = "Q"
        Case "18"
            strLetter = "R"
        Case "19"
            strLetter = "S"
        Case "20"
            strLetter = "T"
        Case "21"
            strLetter = "U"
        Case "22"
            strLetter = "V"
        Case "23"
            strLetter = "W"
        Case "24"
            strLetter = "X"
        Case "25"
            strLetter = "Y"
        Case "26"
            strLetter = "Z"
        Case "27"
            strLetter = "AA"
        Case "28"
            strLetter = "BB"
        Case "29"
            strLetter = "CC"
        Case "30"
            strLetter = "DD"
        
   End Select
 
           '--> Insert data in MS Word template
            
            If Len(rsCustomerTable!Name) <> 0 Then
                WordDoc.Bookmarks("" & strLetter & "1").Range.Text = rsCustomerTable!Name
                Debug.Print rsCustomerTable!Name
            End If
            
            If Len(rsCustomerTable!Address) <> 0 Then
            WordDoc.Bookmarks("" & strLetter & "2").Range.Text = rsCustomerTable!Address
                Debug.Print rsCustomerTable!Address
            End If
            
            If Len(rsCustomerTable!City) <> 0 Then
            WordDoc.Bookmarks("" & strLetter & "3").Range.Text = rsCustomerTable!City
                Debug.Print rsCustomerTable!City
            End If
            
            If Len(rsCustomerTable!State) <> 0 Then
            WordDoc.Bookmarks("" & strLetter & "4").Range.Text = rsCustomerTable!State
                Debug.Print rsCustomerTable!State
            End If
                
            If Len(rsCustomerTable!Zip) <> 0 Then
            WordDoc.Bookmarks("" & strLetter & "5").Range.Text = rsCustomerTable!Zip
                Debug.Print rsCustomerTable!Zip
            End If
            
            rsCustomerTable.MoveNext
            
            vbalProgressBar1.Max = rsCustomerTable.RecordCount
            vbalProgressBar1.Value = intTimes
            
            intTimes = intTimes + 1
            
        Loop
End With
 
        '--> Save new document and quit MS Word
        
        MsgBox "There are " & rsCustomerTable.RecordCount & " labels to Print, " & strUser & "", vbOKOnly, "Label Count"
        
        WordDoc.SaveAs "F:\Shared\Cust Serv\Programs\LabelCreatorPlus\testLabels.doc"
        'WordDoc.PrintOut
        WordDoc.Close (Word.WdSaveOptions.wdDoNotSaveChanges)
        WordApp.Quit
 
    Set WordDoc = Nothing
    Set WordTemp = Nothing
    Set WordApp = Nothing
    
    CloseDatabase
   
        End
        
End Sub

You cant as a new page would require you to add/create new bookmarks on the new page. Think of it as a website. If you add a new page can you use links on another page on the new page to point to the new page? Nope.

Would it be best just to expand the template to accept 60 labels with preset bookmarks?

Either expand the template (but what if they add a third page?) or write code to dynamically create new additional bookmarks, if possible, if there is text already on the new page.

You could take the top 30 off the list for each page.

That makes no sense as pages are not separate from the document object. I dont think you understand the question or the code. The code is in VB6 if you didnt notice and the loop populates the bookmarks across the document and not per page. If you dont have the bookmarks pre-created for additional pages then you will generate an error when trying to access a non-existant bookmark.

You're right, Rob.... This is what I've decided to do....

If there are more than 30 records, I want to loop through until it reaches 30, SavePrintClose (Through a function). With the document closed but the App still open, I want to reopen the same template (with the same bookmarks)start the loop again, but this time start at record 31, then re-save the doc with an incrimented number....

Here's the code:

VB Code:

Public Sub cmdGetLabels_Click()
 
    '--> Get template and document storage path
 
    template$ = getTemplate
 
    ConnectDatabase
 
    '--> Start MS Word and open template
 
 
    OpenWordDoc 1
    
    intTimes = 1
 
With rsCustomerTable
 
    Do Until intTimes = .EOF
        
    Select Case intTimes
 
        Case "1"
            strLetter = "A"
        Case "2"
            strLetter = "B"
        Case "3"
            strLetter = "C"
        Case "4"
            strLetter = "D"
        Case "5"
            strLetter = "E"
        Case "6"
            strLetter = "F"
        Case "7"
            strLetter = "G"
        Case "8"
            strLetter = "H"
        Case "9"
            strLetter = "I"
        Case "10"
            strLetter = "J"
        Case "11"
            strLetter = "K"
        Case "12"
            strLetter = "L"
        Case "13"
            strLetter = "M"
        Case "14"
            strLetter = "N"
        Case "15"
            strLetter = "O"
        Case "16"
            strLetter = "P"
        Case "17"
            strLetter = "Q"
        Case "18"
            strLetter = "R"
        Case "19"
            strLetter = "S"
        Case "20"
            strLetter = "T"
        Case "21"
            strLetter = "U"
        Case "22"
            strLetter = "V"
        Case "23"
            strLetter = "W"
        Case "24"
            strLetter = "X"
        Case "25"
            strLetter = "Y"
        Case "26"
            strLetter = "Z"
        Case "27"
            strLetter = "AA"
        Case "28"
            strLetter = "BB"
        Case "29"
            strLetter = "CC"
        Case "30"
            strLetter = "DD"
   
   End Select
 
        '--> Insert data in MS Word template
 
        If Len(rsCustomerTable!Name) <> 0 Then
           WordDoc.Bookmarks("" & strLetter & "1").Range.Text = rsCustomerTable!Name
            Debug.Print rsCustomerTable!Name
        End If
        
        If Len(rsCustomerTable!Address) <> 0 Then
        WordDoc.Bookmarks("" & strLetter & "2").Range.Text = rsCustomerTable!Address
            Debug.Print rsCustomerTable!Address
        End If
        
        If Len(rsCustomerTable!City) <> 0 Then
        WordDoc.Bookmarks("" & strLetter & "3").Range.Text = rsCustomerTable!City
            Debug.Print rsCustomerTable!City
        End If
        
        If Len(rsCustomerTable!State) <> 0 Then
        WordDoc.Bookmarks("" & strLetter & "4").Range.Text = rsCustomerTable!State
            Debug.Print rsCustomerTable!State
        End If
        
        If Len(rsCustomerTable!Zip) <> 0 Then
        WordDoc.Bookmarks("" & strLetter & "5").Range.Text = rsCustomerTable!Zip
            Debug.Print rsCustomerTable!Zip
        End If
        
        rsCustomerTable.MoveNext
        
        vbalProgressBar.Max = rsCustomerTable.RecordCount
        vbalProgressBar.Value = intTimes
        
        intTimes = intTimes + 1
        
        Loop
        
    End With
        
        SavePrintClose 1
 
[B][color=red][size=4]This is where I want to go back through the loop for records 31-60....[/size][/color][/B][color=red][size=4][/size][/color][size=4][/size]        
 
End Sub

Moved from Classic VB forum.


After this line is where you will need to cut up the recordset into chunks of 30...

VB Code:

Do Until intTimes = .EOF

You will want to check the recordcount and if its divisible by 30 evenly then call out to a function to save the doc and reopen a new template setting the word doc object variable to it. You can use the Mod function for testing divisibility by 30.

I'll leave you in the capable hands of RobDog, but I just thought I'd mention that your Select Case could be made much shorter and still have the same effect:

VB Code:

Select Case intTimes
        Case 1 To 26
            strLetter = Chr(64 + intTimes)
        Case 27 To 30
            strLetter = String(2, 64 - 26 + intTimes)
        End Select