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

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

Who is Participating?
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.
psokoloAuthor Commented:
WOW ... thats great ... Thank you a lot.
You're welcome.  The format codes are certainly counter intuitive.  Thanks for the points.  :)
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.