none
XL 2010 macro mishandles date, page in footer RRS feed

  • Question

  •  Environment:
      Office 2010
      Windows 7
     
    This code in a macro:

         Application.PrintCommunication = False 
         With ActiveSheet.PageSetup 
             .LeftFooter = "&D" 
             .CenterFooter = "" 
             .RightFooter = "Page &P" 
         End With 
         Application.PrintCommunication = True 
     

    Is producing '%R' in the left footer and nothing in the right footer.  Any idea why?
    Assume I must be doing something wrong.
    Thanks.

    Jeff Kantner

    • Edited by JJinCA Tuesday, January 17, 2012 10:07 PM
    • Moved by Max Meng Wednesday, January 18, 2012 4:04 AM Moving to a more appropriate forum (From:Excel IT Pro Discussions)
    Tuesday, January 17, 2012 10:06 PM

Answers

  • Hi Jeff,

     

    Unfortunately I am not able to test in Excel 2010, but in Excel 2007 it works, allthough I skipped the Application.PrintCommunication part:

     

    Sub test()
    
    '     Application.PrintCommunication = False
         With ActiveSheet.PageSetup
             .LeftFooter = "&D"
             .CenterFooter = ""
             .RightFooter = "Page &P"
         End With
     '    Application.PrintCommunication = True
    
    End Sub
    


    Results:

     

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Wednesday, January 18, 2012 5:24 AM
    Moderator

All replies

  • Hi Jeff,

     

    Unfortunately I am not able to test in Excel 2010, but in Excel 2007 it works, allthough I skipped the Application.PrintCommunication part:

     

    Sub test()
    
    '     Application.PrintCommunication = False
         With ActiveSheet.PageSetup
             .LeftFooter = "&D"
             .CenterFooter = ""
             .RightFooter = "Page &P"
         End With
     '    Application.PrintCommunication = True
    
    End Sub
    


    Results:

     

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Wednesday, January 18, 2012 5:24 AM
    Moderator
  • Daniel --

    I am seeing an issue similar to Jeff's with Excel 2010.  I have a subroutine labeled SetPrintArea that contains:

    Private Sub SetPrintArea(ByRef ws As Worksheet)
        ws.PageSetup.PrintArea = ws.UsedRange.Address
        Application.PrintCommunication = False
        With ws.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
            .LeftHeader = ""
            .CenterHeader = GetReportHeader(ws.Name)
            .RightHeader = ""
            .LeftFooter = "&D"
            .CenterFooter = "&T"
            .RightFooter = "Page &P of  &N "
            .LeftMargin = Application.InchesToPoints(0.7)
            .RightMargin = Application.InchesToPoints(0.7)
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .PrintHeadings = False
            .PrintGridlines = True
            .PrintComments = xlPrintNoComments
            .PrintQuality = 600
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 10
            .PrintErrors = xlPrintErrorsDisplayed
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = True
            .EvenPage.LeftHeader.Text = ""
            .EvenPage.CenterHeader.Text = ""
            .EvenPage.RightHeader.Text = ""
            .EvenPage.LeftFooter.Text = ""
            .EvenPage.CenterFooter.Text = ""
            .EvenPage.RightFooter.Text = ""
            .FirstPage.LeftHeader.Text = ""
            .FirstPage.CenterHeader.Text = ""
            .FirstPage.RightHeader.Text = ""
            .FirstPage.LeftFooter.Text = ""
            .FirstPage.CenterFooter.Text = ""
            .FirstPage.RightFooter.Text = ""
        End With
        Application.PrintCommunication = True
    End Sub
    
    

    This is based upon a recorded macro.  The footer dialog box looked very similar to image.

    However after running the code the footer now looks like this:

    It does not appear that Excel 2010 VBA interpreted &D, &T or &N correctly.

    Any help is appreciated.

    - Dennis

     

    Tuesday, January 24, 2012 8:46 PM
  • Hi Dennis,

     

    Unfortunately I don't have Excel 2010 to test with, but it might be a bug in combination with using the Application.PrintCommunication which is introduced in Excel 2010 to speed up the communication with the PageSetup object.

     

    The Macro recorder places these lines automatically, first set to False, then to True.

     

    Try to run without these lines to see if that resolves your problem.

     

    Best regards,



    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Wednesday, January 25, 2012 3:08 AM
    Moderator
  • Hi Dennis,

    Unfortunately I don't have Excel 2010 to test with, but it might be a bug in combination with using the Application.PrintCommunication which is introduced in Excel 2010 to speed up the communication with the PageSetup object.

    The Macro recorder places these lines automatically, first set to False, then to True.

    Try to run without these lines to see if that resolves your problem.

    Best regards,



    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    I came across this thread as I had the same problem using Excel 2010. After removing the Application.PrintCommunication commands, it worked like a champ.
    Wednesday, February 6, 2013 9:42 PM
  • Yup, I encountered the same issue. 

    danishani's solution is workable. Thanks a lot.


    星星照亮全世界

    Thursday, May 8, 2014 7:02 AM
  • Hi Daniel, 

    I am having similar issue with Excel 2010 and I tried to comment out the line Application.PrintCommunication  but still having the same problem as before. 

    My code for the footer is as follows:

     .LeftFooter = "&F &A"
     .CenterFooter = "Page &P of &N"
     .RightFooter = "&D &T"

    It doesn't recognize the 2nd part e.g. &A, &N and &T are ignored while printing. 

    Any further update on this issue? 

    Thanks for your help,

    Chetan 

    Friday, January 15, 2016 5:31 PM