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.

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

Thanks Will,

 

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

 

Cheers

Jane

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

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

I've experienced the same problem too. VBA looks just fine - it was driving me nuts!  Will very much look forward to the solution.

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.

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

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.

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

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.

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.

Thanks Jeff this works!!

• Proposed as answer by Tomcat49 Monday, February 06, 2012 12:41 PM
• Unproposed as answer by Tomcat49 Monday, February 06, 2012 12:42 PM

Thank you, thank you, thank you!

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?

Worked perfectly for me.  Thanks

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.

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

Is there any solution or patch to download for this yet?

~

Hi Will,

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

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.

• Edited by Enquirylearner Sunday, August 31, 2014 2:32 AM

THANK YOU SO MUCH!  This fixed my problem.    This is going to save me so much time! :)