Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2005-04-20
Medium Priority
Last Modified: 2010-05-02
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 ??

Question by:psokolo
  • 2
LVL 38

Accepted Solution

PaulHews earned 1000 total points
ID: 13829382
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.

Author Comment

ID: 13829413
WOW ... thats great ... Thank you a lot.
LVL 38

Expert Comment

ID: 13829433
You're welcome.  The format codes are certainly counter intuitive.  Thanks for the points.  :)

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question