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

Posted on 2005-04-20
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
    LVL 38

    Accepted Solution

    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

    WOW ... thats great ... Thank you a lot.
    LVL 38

    Expert Comment

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

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now