How to auto-populate contents page?

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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 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 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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote