locked
Excel 2010 Header and Footer Bug - Macro recording RRS feed

  • Question

  • I have tried to record a macro in Excel 2010 to put in a custom header/footer.

    1. Recording the Macro, stored in Personal Macro Workbook.

    2. Go to Page Setup and Custom Header (or Footer)

    3. Inserted the Date/Time field in the left. Inserted the Filepath in the right.

    4. Closed header/footer

    5. Stopped Macro recording.

    When the macro is rerun, and viewed in Page Layout, odd codes appear &&R and the path appears on the left.

     

    Curious to know if this is a new bug, as this macro used to work perfectly in all prior versions of Excel (I'm a trainer and we've been doing this macro for many versions). Tried recording the macro through the dialog box and through the Page Layout view, and saving in this workbook, and in personal macro workbook. No luck in seeing the expected outcome when it is run via the macro.

    Thursday, December 16, 2010 8:26 AM

Answers

  • I had the same problem, and think I fixed it.  Here is a section of my code that I believe was causing the issue:

     

    Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintTitleRows = "$1:$7"
            .PrintTitleColumns = ""
        End With
        Application.PrintCommunication = True

    The problem was with the "application.printcommunication = false" and "application.printcommunication = true" lines.  These are new to 2010.  In fact, when you try to run a macro with this code in 2007, it doesn't work.  I found that by deleting all of these "printcommunication" lines, the macro works just fine.  A bit of a pain but not the end of the world.  Hope this helps.

    • Proposed as answer by Per Jessen Wednesday, October 19, 2011 11:48 AM
    • Marked as answer by janepettigrew Thursday, February 16, 2012 12:52 AM
    Friday, June 24, 2011 7:13 PM

All replies

  • Hello janepettigrew,

    This is Will with Microsoft Excel Support. I have tested your repro steps and I can reproduce the problem on my machine. I believe you have found a genuine bug. I am going to go ahead and submit a bug report for this issue. As soon as I hear back on the bug report from the developers I'll update this thread. It may be some time, however, before I hear back from them.

    Will Buffington
    Microsoft Excel Support

    • Marked as answer by Sally Tang Thursday, December 23, 2010 5:20 AM
    • Unmarked as answer by janepettigrew Thursday, February 16, 2012 12:53 AM
    Tuesday, December 21, 2010 12:30 AM
  • Thanks Will,

     

    Looking forward to a resolution to this, as this is something pretty common we do.

     

    Cheers

    Jane

    Tuesday, December 21, 2010 8:59 AM
  • Hi Will - did you have any luck with Janes questions re: Macro for Header and Footer - I was training a group of people on Excel 2010 toady and had the same problem - recording macro to enter a custom footer i.e. filename & Path on left and date on right and neither one came out correctly when I ran the macro.  Embarrasing!! 

    Any help would be greatly appreciated.

     

    Kind regards

    Ann

    Wednesday, March 2, 2011 4:22 PM
  • Any resolution to this problem?  We produce 100's of spreadsheet plots and rely on being able to put the filename and tab in the footer in order to keep track of where each plot came from.  This seems like just another reason for me to recommend to my clients to stay with 2003.  Other than the increase in the number of rows in Excel, (which really helps us) no one in our office can see any improvement over 2003, but we see a lot of problems, the least of which is that every command (if you can find the command at all) takes twice as many keystrokes to implement.  One step forward and 5 steps back, technology marches on!

    Tom


    Tom
    Thursday, March 24, 2011 11:55 PM
  • I've experienced the same problem too. VBA looks just fine - it was driving me nuts!  Will very much look forward to the solution.
    Tuesday, May 17, 2011 7:58 PM
  • Same problem as all above have reported - could do with a solution.

    However, I have a workaround until we get the proper answer.

    PART 1 - Write the macro as the codes would appear (don't use the Macro Recorder). Use &[Date] etc

    PART 2 - "Touch" (enter and then exit)  the custom header/footer menu - this changes the codes to what we want. This can be automated with ...SendKeys. The key entries are as you would type on the keyboard

    Here is my example:

    Sub CustomFooter()
    '
    ' CustomFooter Macro
    ' Add a custom footer to current worksheet
    ' 13/5/2011

    'NOTE: Now from the spreadsheet touch the footers and they will then be OK
       
        With ActiveSheet.PageSetup
            .LeftFooter = "&9Printed on &[Date] &[Time]"
            .CenterFooter = "Page &[Page] of &[Pages]"
            .RightFooter = "&9&[File] &[Tab]"
        End With
        'Touch the footer menu to correct the bug and make the footer work
        Application.SendKeys ("%PSOH%U{TAB}{TAB}{TAB}{ENTER}{ENTER}")
       
    End Sub

    Good luck - works for me. Take care with the SendKeys though.

    Thursday, May 19, 2011 3:33 PM
  • I had the same problem, and think I fixed it.  Here is a section of my code that I believe was causing the issue:

     

    Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintTitleRows = "$1:$7"
            .PrintTitleColumns = ""
        End With
        Application.PrintCommunication = True

    The problem was with the "application.printcommunication = false" and "application.printcommunication = true" lines.  These are new to 2010.  In fact, when you try to run a macro with this code in 2007, it doesn't work.  I found that by deleting all of these "printcommunication" lines, the macro works just fine.  A bit of a pain but not the end of the world.  Hope this helps.

    • Proposed as answer by Per Jessen Wednesday, October 19, 2011 11:48 AM
    • Marked as answer by janepettigrew Thursday, February 16, 2012 12:52 AM
    Friday, June 24, 2011 7:13 PM
  • Any news back on this problem yet Will, I've just had the same problem using the filename in the footer, was fine with normal text in the header but wouldn't display the filename when the code was inserted in the footer. It always worked fine in 2007 - I also tried to insert a picture logo in the footer and that didn't display either when the Macro was run.
    Thursday, July 7, 2011 6:27 PM
  • Thank you Jeff98!

    at last a remedy which works!

    Sad however, that official support does not come up with any solution.

    In any case: do you have an idea, why these Print communication lines are in the macro. What do they do (apart from interferring with the footer)?

    Sunday, August 28, 2011 12:20 PM
  • I want to also Thank You Jeff.

    Eventhough encountering this issue led me to learning a hell-of-a-whole lot more about Macros than I knew before, I've probably wasted 2 days trying to find a solution to this problem. I was just about to give up when, by the power of Grayskull, I landed on this site. THANKS A MILLION!

    O.

    Tuesday, November 1, 2011 9:23 PM
  • Sorry, I cannot agree that Jeff98 has solved the problem.

    When I record the macro in Excel 2010 (SP1 MSO) as janepettygrew reported, it will look like this:

        ActiveWindow.View = xlPageLayoutView
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .LeftHeader = "&D"
            .RightHeader = "&P&N"
        End With
        Application.PrintCommunication = True

    I expect the left header to show the date and the right header to show the path and filename.

    However, when I run the macro, the left header becomes: &&R&[path] and the right header remains void. This is certainly not what I recorded. No filename anywhere.

    When I delete the print communication lines and run the macro again, the left header becomes &[date], which jeff98 may consider as successful, but the right header becomes &[page]&[pages]. This is again not what I recorded and again no filename anywhere.

    Are there any indications how long we may have to wait until Will Buffington can tell us what he heard back on his bug report?

    T.

    Tuesday, November 8, 2011 4:03 PM
  • Thanks Jeff this works!!
    • Proposed as answer by Tomcat49 Monday, February 6, 2012 12:41 PM
    • Unproposed as answer by Tomcat49 Monday, February 6, 2012 12:42 PM
    Friday, November 11, 2011 12:53 AM
  • Thank you, thank you, thank you!
    Monday, March 19, 2012 10:23 PM
  • Any progress on this yet?  

    As I understand it, PrintCommunication was added in 2010 to avoid individual calls to the printer while setting PageSetup properites, and thus greatly reducing the delay in code execution.  The previous work-around had been to use an XL4 macro call to set all of the properties in a single step.  Microsoft's "fix" apparently introduced many problems with the actual content produced in headers and footers.  

    It does seem that by NOT suspending printer communication (eliminate the "PrintCommunication =" lines from your code) avoids the header/footer issue, but that still leaves the speed issue.  This may not be a big deal to some, but when you have numerous pages to setup, the delay is very noticeable and annoying.  

    Also, the fact that the macro recorder inserts the PrintCommunication lines is aggravating, given that they do not produce workable code.  

    Can we submit an official, trackable bug report on this?

    Wednesday, May 30, 2012 2:27 PM
  • Worked perfectly for me.  Thanks
    Wednesday, June 20, 2012 6:45 PM
  • I had the same problem and worse.  Sometimes the new heading info was used, other times not and sometimes appended.  My solution is not elegant but it works.

    Make all Application.PrintCommunication settings = True

    This dramatically slows down the Print (in my case pdf output) but it works for me.


    Bruce Albrecht

    Friday, July 27, 2012 12:35 PM
  • I had the same problem and worse.  Sometimes the new heading info was used, other times not and sometimes appended.  My solution is not elegant but it works.

    Make all Application.PrintCommunication settings = True

    This dramatically slows down the Print (in my case pdf output) but it works for me.


    Bruce Albrecht

    Friday, July 27, 2012 12:37 PM
  • Apparently, this "bug" is still causing problems.

    Here's my situation. At my job, I have a workbook with 3 separate worksheets of client information. In this file, I have a macro that combines all of that information onto one Master worksheet. I also have a macro to hide certain rows. Also, there are a couple of macros to hide specific sets of columns, depending on why someone accesses the client list. All of these work perfectly.

    My problem is the last macro. It sets ALL of the Page Setup Properties for the Master worksheet. However, it won't insert the header or footer correctly. The Right Footer should insert a page number; but, actually does nothing. The Center Header should read "Client List," on the first line, and "as of" with the current date inserted on the second line. What I get is:

    "Client List,
    as o"

    Here's the code that I'm using:

    Sub Margins()
    '
    ' Margins Macro
    '

    '
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
        Application.PrintCommunication = True
        Application.PrintCommunication = False
        ActiveSheet.PageSetup.PrintArea = ""
        With ActiveSheet.PageSetup
            .LeftHeader = ""
            .CenterHeader = "Client List," & Chr(10) & "as of &D"
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = "&P"
            .LeftMargin = Application.InchesToPoints(0.5)
            .RightMargin = Application.InchesToPoints(0.5)
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.5)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .PrintHeadings = False
            .PrintGridlines = False
            .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 = 0
            .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

    Friday, September 14, 2012 9:45 PM
  • Is there any solution or patch to download for this yet?

    ~

    Sunday, April 21, 2013 10:15 AM
  • Hi Will,

    Can you let the "Word" Support people know that "Word" has the same issues?  Thank you, appreciate it.

    Friday, May 10, 2013 7:42 PM
  • Appreciate this site - like earlier participants - went to show a class what I have delivered for many years - only to find one lone '&' where field names should have appeared.  So pleased it was something 'out of my control'.

    Based on all the guidance already given - what I have now done is to change all of the communication lines to True except for the last one - made it False.  This provided me the layout I had inserted eg tab sheet name in header, file name, page number and date all in the places they were supposed to be in the footer.  When I had removed the communication lines previously the file name would not appear at all. 

    Sub Macro3()
    '
    ' Macro3 Macro
    '
    ' Keyboard Shortcut: Ctrl+i
    '
        Application.PrintCommunication = True
        With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
        Application.PrintCommunication = True
        ActiveSheet.PageSetup.PrintArea = ""
        Application.PrintCommunication = True
        With ActiveSheet.PageSetup
            .LeftHeader = ""
            .CenterHeader = "&A"
            .RightHeader = ""
            .LeftFooter = "&F"
            .CenterFooter = "&N"
            .RightFooter = "&D"
            .LeftMargin = Application.InchesToPoints(0.708661417322835)
            .RightMargin = Application.InchesToPoints(0.708661417322835)
            .TopMargin = Application.InchesToPoints(0.748031496062992)
            .BottomMargin = Application.InchesToPoints(0.748031496062992)
            .HeaderMargin = Application.InchesToPoints(0.31496062992126)
            .FooterMargin = Application.InchesToPoints(0.31496062992126)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .PrintQuality = 600
            .CenterHorizontally = True
            .CenterVertically = True
            .Orientation = xlLandscape
            .Draft = False
            .PaperSize = xlPaperA4
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = 140
            .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 = False
    End Sub

    Have tested a number of times - continues to work.  My only concern was for students at an introductory level to Excel - having to take them into the macro to customise is not really satisfactory.

    Hope this suggestion works for you all.




    Friday, August 29, 2014 11:08 PM
  • THANK YOU SO MUCH!  This fixed my problem.    This is going to save me so much time! :)
    Wednesday, March 25, 2015 3:33 PM
  • This does slow things down quite a bit. I find it is almost as fast to change it myself then to run the macro. I run other macros on worksheets I receive that are far more complex, and they are quick. Since I'm running this on 200 spreadsheets/month, I'd like it to be fixed. I'm running Office 365, so 2016 now and still a problem.
    Tuesday, March 27, 2018 6:32 PM
  • What worked for me was to use a variable, which caused the header to update immidiately. In my case the filename wouldn't show. So after trying many things that didn't work for me I changed from:

          

    Sub InsertHeader()
        ActiveSheet.PageSetup.LeftHeader = "&[file]"
    End Sub

    to:

    Sub InsertHeader()
        Dim filename As String
        filename = ActiveWorkbook.Name
        ActiveSheet.PageSetup.LeftHeader = filename
    End Sub

     This worked for me. Hope it helps someone!           



    • Edited by DocMarten Friday, September 14, 2018 11:03 AM
    Friday, September 14, 2018 9:46 AM