none
VB.NET - Finding Excel's Total Page Counter RRS feed

  • Question

  • I'm using VB.NET, upon a button click event, I want to reset the internal total page counter in Excel. With this button click event, I am generating a table of contents, and the page numbering doesn't start until the first page after the TOC...plus, there are various pages prior to the TOC that I don't want included in the page count. So, there might be a total of 20 printable pages in the workbook, but only 16 listed on the TOC.

    For example - we can set up a custom Header/Footer with  "Page &[Page] of &[Pages]"  I want to use VB.NET to find the  "&[Pages]"  value, and then reset it to a programmatically specific value. Resulting in every page reading something like  "Page 1 of 16", "Page 2 of 16", "Page 3 of 16", etc. Even if there are 20 pages in the workbook.

    Any thoughts?

    Wednesday, February 11, 2015 6:56 PM

Answers

  • Thanks for the assistance.

    This seems to be working for me at the moment:

                sht.PageSetup.RightFooter = "Page &P of " & PgCnt
    

    • Marked as answer by fdegree Thursday, February 12, 2015 5:09 PM
    • Edited by fdegree Thursday, February 12, 2015 5:10 PM
    Thursday, February 12, 2015 5:09 PM

All replies

  • Hi fdegree,

    Are you going to develop a, Excel Add-in application? Or just use Excel interop in your VB.NET application?

    As far as I know, by default we can't create TOC in Excel workbooks. So could you please explain how did you generate the table of contents?

    This is an article which is talking about how to generate TOC in Excel workbooks, the code is in VBA, but probably it'll give you some ideas.

    Add a table of contents to your workbook


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 12, 2015 9:25 AM
    Moderator
  • Thanks for the assistance.

    This seems to be working for me at the moment:

                sht.PageSetup.RightFooter = "Page &P of " & PgCnt
    

    • Marked as answer by fdegree Thursday, February 12, 2015 5:09 PM
    • Edited by fdegree Thursday, February 12, 2015 5:10 PM
    Thursday, February 12, 2015 5:09 PM
  • Hi fdegree,

    Are you going to develop a, Excel Add-in application? Or just use Excel interop in your VB.NET application?

    As far as I know, by default we can't create TOC in Excel workbooks. So could you please explain how did you generate the table of contents?

    I just realized I never actually answered your question...This is a COM Add-in. Upon the custom ribbon button click event, I check for the existence of a TOC sheet, and warn if one already exists, then a form opens, asking for 2 things: 1.) a name to be placed at the top of the TOC sheet, and 2.) how many existing worksheets are to be prior to the TOC placement. Here is the TOC code:

            Dim MyInput As String
            Dim MyPageInput As String
            Dim Warning As DialogResult
    
            'Get the name of the project the user entered in to the text box for TOC title
            MyInput = TextBox1.Text
            If MyInput = "" Then
                Warning = MsgBox("You neglected to enter your PROJECT NAME in the box.  Please try again.", MsgBoxStyle.OkOnly + vbCritical, "WARNING...PLEASE READ!!!!!")
                If Warning = DialogResult.OK Then
                    Exit Sub
                End If
            End If
    
            'Get the page number that the user entered into the text box
            MyPageInput = TextBox2.Text
            If MyPageInput = "" Then
                Warning = MsgBox("You neglected to enter the PAGE LOCATION in the box.  Please try again.", MsgBoxStyle.OkOnly + vbCritical, "WARNING...PLEASE READ!!!!!")
                If Warning = DialogResult.OK Then
                    Exit Sub
                End If
            End If
            Me.Close()
    
            'Convert MyPageInput to integer
            Dim PageToInteger As Integer = Integer.Parse(MyPageInput)
    
            'Insert new TOC sheet
            Dim Sheet As Worksheet
            Dim wSheet2 As Worksheet
    
            Sheet = Globals.ThisAddIn.Application.Sheets(PageToInteger)
            wSheet2 = New Worksheet
            wSheet2 = Globals.ThisAddIn.Application.Sheets.Add(After:=Sheet)
            wSheet2.Name = "Table Of Contents"
    
            'Format the table of contents page
            wSheet2.Range("A1").Font.Name = "Tribune"
            wSheet2.Range("A1").Font.Size = "16"
            wSheet2.Range("A1").Font.Bold = True
            wSheet2.Range("A3").Font.Bold = True
            wSheet2.Range("B5").Font.Bold = True
            wSheet2.Range("D5").Font.Bold = True
            wSheet2.Rows(2).Cells.Font.Size = "4"
            wSheet2.Rows(4).Cells.Font.Size = "4"
            wSheet2.Rows(6).Cells.Font.Size = "4"
            wSheet2.Columns(4).Cells.HorizontalAlignment = Constants.xlCenter
            wSheet2.Range("A1").Value = MyInput
            wSheet2.Range("A3").Value = "Table of Contents"
            wSheet2.Range("B5").Value = "System"
            wSheet2.Range("D5").Value = "Page Number"
            wSheet2.Columns(2).ColumnWidth = "35"
            wSheet2.Columns(3).ColumnWidth = "15"
            wSheet2.Columns(4).ColumnWidth = "15"
            wSheet2.Columns(7).ColumnWidth = "35"
    
            'Prepare to scroll through Worksheets
            Dim sht As Worksheet
            Dim i As Integer
            Dim rg As Range
            Dim Page As Integer
            Dim PgCnt As Integer
            Dim HPages As Integer
            'Dim VPages As Integer
    
            MsgBox("This could take a couple of minutes, depending upon the size of the report.  Please be patient.")
    
            Dim PageToInteger1 As Integer
            PageToInteger1 = PageToInteger
            i = 0
            PgCnt = -PageToInteger1 - 1
    
            'Start scrolling through Worksheets
            For Each sht In Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets
    
                'Set "First page number" value to auto
                sht.PageSetup.FirstPageNumber = Excel.Constants.xlAutomatic
    
                HPages = sht.HPageBreaks.Count + 1
                'All of the worksheets have horizontal pagebreaks so I'm not using VPageBreaks in this code
                'VPages = sht.VPageBreaks.Count + 1
                Page = HPages
    
                'Populate TOC sheet with the necessary info from this sheet
                wSheet2.Range("B7").Offset(i).Value = sht.Name
                wSheet2.Range("D7").Offset(i).NumberFormat = "@"
                If Page = 1 Then
                    wSheet2.Range("D7").Offset(i).Value = PgCnt + 1
                Else
                    wSheet2.Range("D7").Offset(i).Value = PgCnt + 1 & " - " & PgCnt + Page
                End If
    
                'Create hyperlink for each sheet on the TOC
                wSheet2.Range("G7").Offset(i).Value = sht.Name
                With wSheet2
                    .Hyperlinks.Add(Anchor:=.Range("G7").Offset(i), Address:="", SubAddress:="'" & sht.Name & "'!A1")
                End With
    
                PgCnt = PgCnt + Page
                i = i + 1
    
            Next sht
    
            'Set the "First Page Number" of the first sheet after the TOC to "1"
            Dim FirstSheet As Worksheet
    
            FirstSheet = Globals.ThisAddIn.Application.Sheets("Table Of Contents").Next
            FirstSheet.PageSetup.FirstPageNumber = 1
    
            'Set the Print Area before the TOC sheet is modified
            wSheet2.PageSetup.PrintArea = "$A$1:$E$" & i + 6
    
            'Delete all rows prior to, and including, the TOC reference
            Dim PageToInteger2 As Integer
            PageToInteger2 = PageToInteger + 7
            rg = wSheet2.Rows("7:" & PageToInteger2)
            rg.Delete()
    
            'Return to Table of Contents sheet
            wSheet2.Range("A1").Select()
    
            'Let user know the TOC is complete
            MsgBox("Table Of Contents is complete.")

    Saturday, February 14, 2015 3:16 PM