Results 1 to 6 of 6

Thread: Page Numbers In PDF

  1. #1

    Page Numbers In PDF



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear Ken Plus,

    Could you please tell me how to add page numbers to the PDF file by using VB code?


    Thanks,
    Richard

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Hi Richard,

    The usual way that I do page numbering is to set up the page numbers in the header/footer in Excel. When you print the pages to PDF, then it prints those page numbers. Is that not working for you?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    Smile

    Hi Ken,

    I do the same thing and it works. The only one thing not good is the font size changes with different Excel print area page set ups. I print Excel book in PowerPoint also, those numbers are generated by PowerPoint and thay all are same size and look better.


    Thanks,
    Richard

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Ah, I get it.

    To be honest, I'm not sure. The default setup will obviously scale... Even adding a text box wouldn't help, as it would scale with a "shrink to fit" scenario..

    I don't really know what to suggest, to be honest.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Actually there is another issue, when an Excel sheet data region is large, one sheet will break into multiple pages, so the page number may go out of order. I need to find a way to count page number if page splits.

    Thanks.

    Richard

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    Hi Richard,

    As the issue has become a bit different than the original thread you started, I've split this into a new thread. Bascially we're looking for a way to control the page numbering via VBA now.

    The only ways I can see to do this is are to use VBA to:
    • Change the header/footer on the sheets before you print them to the correct page number
    • Actually put the page numbers in the worksheet. (Personally I'd stay well away from this as it would be a pain to do.)
    I'll let you record a macro to set a page header/footer to get the code to set page numbers. It will be the page counting that's the issue for you. You can get the count of pages for a worksheet using the following code:

    Code:
    Dim ws as Worksheet
    Dim lPages as Long
     
    Set ws = Activesheet
    lPages = Activesheet.PageSetup.Pages.Count
    Unfortunately though, I don't believe you can set the initial page count using a variable, so if you're printing multiple sheets, you may have to do them individually, setting the page number for each page. The kicker is that the PageSetup stuff is unbelievably S-L-O-W. So you may suffer in that case.

    Curious... can you chapterize it? What I mean is set up your numbering so that it uses worksheet counting, but is prefaced with an index?

    i.e. 10-x, 20-x, 30-x

    x would be the variable page number per the headers and footers, but you could manually declare the preface in the order you wanted to see it? (This is the way we index working papers in accounting to avoid having to renumber the whol file every time we add more support pages.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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