Question on Excel automation in VB ... cant input page count.

Hi
I am trying to input automatically a page count in a footer of an excel speadsheet.
I am using the following code :

xlsSheet.PageSetup.RightFooter = "Page &[Page] of &[Pages]"

However it doesnt work. The code actually puts in this string in the footer but when I open the excell speardsheet it wouldnt show page count but the string itself.
I need to go to page setup/footer and I dont even have to change the string, only click ok and page starts showing.

Any ideas ??

thanks,
Pawel
psokoloAsked:
Who is Participating?
 
PaulHewsCommented:
There are special codes for the footer.  You can find them in the help
For this example:
xlsSheet.PageSetup.RightFooter = "Page &P of &N"

List of codes:
Format code      Description
&L      Left aligns the characters that follow.
&C      Centers the characters that follow.
&R      Right aligns the characters that follow.
&E      Turns double-underline printing on or off.
&X      Turns superscript printing on or off.
&Y      Turns subscript printing on or off.
&B      Turns bold printing on or off.
&I      Turns italic printing on or off.
&U      Turns underline printing on or off.
&S      Turns strikethrough printing on or off.
&O      Turns outline printing on or off (Macintosh only).
&H      Turns shadow printing on or off (Macintosh only).
&D      Prints the current date.
&T      Prints the current time.
&F      Prints the name of the document.
&A      Prints the name of the workbook tab.
&P      Prints the page number.
&P+number      Prints the page number plus the specified number.
&P-number      Prints the page number minus the specified number.
&&      Prints a single ampersand.
& "fontname"      Prints the characters that follow in the specified font. Be sure to include the double quotation marks.
&nn      Prints the characters that follow in the specified font size. Use a two-digit number to specify a size in points.
&N      Prints the total number of pages in the document.
0
 
psokoloAuthor Commented:
WOW ... thats great ... Thank you a lot.
Pawel  
0
 
PaulHewsCommented:
You're welcome.  The format codes are certainly counter intuitive.  Thanks for the points.  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.