Page Numbers In PDF

Rich6S

New member
Joined
Apr 27, 2011
Messages
6
Reaction score
0
Points
0
Dear Ken Plus,

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


Thanks,
Richard
 
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?
 
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
 
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.
 
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
 
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.)
 
Back
Top