Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2008
    Location
    Watford UK
    Posts
    104
    Thanks
    1
    Thanked 1 Time in 1 Post

    How to auto-populate contents page?

    This is a puzzle I have been trying to solve with Excel 2007.

    I have worksheets that, when printed, consist of multiple pages. Each page has a footer containing the page number.

    When I prepare the contents page, I put the page numbers in manually. Is there a way of getting Excel to pick this info up automatically?

    David

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The easiset way would be to create the printout in WORD which has creating a table of contents as a feature, as far as I know, excel does not have this feature. You would have to create your own code for doing this and devising a scheme for the code to find the items you want in the TOC.

    If you would like help with this you would have to provide more details about your setup and a sample workbook demonstrating what you need with any potential oddities that would need to be addressed
    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jun 2008
    Location
    Watford UK
    Posts
    104
    Thanks
    1
    Thanked 1 Time in 1 Post
    I am an accountant who uses Excel to produce final accounts for presentation to clients. To use Word would be like me going back over 10 years when I used to use it for final accounts. Excel is soooo much easier.

    I only have clients accounts and I don't think they (the clients) would be very happy to see their accounts on line.

    I suppose all I am really asking is there a way of accessing the contents of a footer?

    David

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Excel is a good tool for accounting.

    Word is good tool for formatting, printing, ToC and so on.

    An answer for you is to continue to prepare the accounts in Excel, then to cut and paste them into Word which can be made to automatically create a ToC linked to print page numbers. You may, of course, have to find the headings you want to show in the ToC and make them into a Heading Style in Word to accomplish what you want.

    You will find advice on how to do that in the Word forum - it could be quite easy, but that depends on how you have structured the accounts in Excel.

    As for putting stuff on-line - indeed you should not do that ! What peope usualy do is to sanitise the data or use dummy data so that helpers can illustrate solutions without having to see real client data.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    As suggested by MartinM you may need to use both. Excel for number crunching and Word for the reports. You can create tables in Word which are live links to excel values or even link particular values depending on your need.

    You don't need to provide proprietary information, just (as I posted) "details about your setup and a sample workbook demonstrating what you need with any potential oddities that would need to be addressed". It can all be made up, we only need to understand how the Table of Contents would be created (eg what VBA would key on for determing a need to mark it in the ToC).

    I don't think you want to access teh contents of the footer as it does not provide the page number, that is determined when the document is printed or in print preview it is not stored anywhere.

    Here is a function that you can use in a routine. When given a cell, the function will return the page it is on within that sheet. You routine just needs to know what to key on to create TOC, find those elements, then use this to get the page number.

    Steve Aprahamian

    Code:
    Option Explicit
    Function PageNumber(rCell As Range)
        Dim iCol As Integer
        Dim iCols As Integer
        Dim lRows As Long
        Dim lRow As Long
        Dim x As Long
        Dim y As Long
     
        Set rCell = rCell.Cells(1)
        With rCell.Parent
            y = rCell.Column
            iCols = .VPageBreaks.Count
            x = 0
            Do
                x = x + 1
            Loop Until x = iCols _
              Or y < .VPageBreaks(x).Location.Column
            iCol = x
            If y >= .VPageBreaks(x).Location.Column Then
                iCol = iCol + 1
            End If
            y = rCell.Row
            lRows = .HPageBreaks.Count
            x = 0
            Do
                x = x + 1
            Loop Until x = lRows _
              Or y < .HPageBreaks(x).Location.Row
            lRow = x
            If y >= .HPageBreaks(x).Location.Row Then
                lRow = lRow + 1
            End If
            If .PageSetup.Order = xlDownThenOver Then
                PageNumber = (iCol - 1) * (lRows + 1) + lRow
            Else
                PageNumber = (lRow - 1) * (iCols + 1) + iCol
            End If
        End With
    End Function

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    iansavell (2011-06-16)

  7. #6
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    That's a great routine Steve. Put a list of named ranges on the cover sheet, like below, and you get an automatically updated TOC.

    Reference1 | =Indirect(A1) & " .........." | =RangeNumber(Indirect(A1))
    Reference2 | =Indirect(A2) & " .........." | =RangeNumber(Indirect(A2))
    Reference3 | =Indirect(A3) & " .........." | =RangeNumber(Indirect(A3))

    It may be worth adding that if the workbook is several sheets, rCell.Parent.Name is the name of the sheet containing the cell.

    Note that this routine introduces a reference to the printer object (to find the page breaks) and so it may make the spreadsheet impossibly slow (see e.g. http://support.microsoft.com/kb/199505. If so, write the TOC formulas in a separate workbook and paste them in at the last minute before printing - wait while the function populates the page breaks.

    Note to self - never forget the power of exhaustive search!

    Ian

  8. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    BUA, VARBERG, SWEDEN, EUROPE
    Posts
    23
    Thanks
    1
    Thanked 1 Time in 1 Post
    Oh yes it is! A piece of cake really. Look for "Header/Footer" in a menu, click on two separate ikons (page# and page in total)
    at your starting page.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •