Blog Comments

  1. simchuck's Avatar
    • |
    • permalink
    I was struggling with this same bug and found your thorough summary. I have seen all of the weird behaviours that have been described, including the "step-through". It's a shame that Microsoft has not yet provided a fix and that we need to develop a workaround for a new feature.

    Since the bug only seems to affect the header and footer definitions, I decided to go with a compromise - sacrificing some of the speed for accuracy in the header/footers. I only turn off PrintCommunication for non- header/footer page setups, and then turn it back on before setting the headers/footers.

    The following code seems to work as intended:


    With ActiveSheet.PageSetup

    ' setup all non- header/footer properties with print
    ' communications suspended to speed page setup
    Application.PrintCommunication = False
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.5)
    .HeaderMargin = Application.InchesToPoints(0.25)
    .FooterMargin = Application.InchesToPoints(0.25)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 300
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 100
    .FitToPagesWide = 1
    .FitToPagesTall = 1

    ' setup all header/footer properties after reinstating
    ' printer communications
    Application.PrintCommunication = True
    .LeftHeader = "This is the Left Header"
    .CenterHeader = "This is the Center Header"
    .RightHeader = "This is the Right Header"
    .LeftFooter = "&F[&A]"
    .CenterFooter = "p. &P of &N"
    .RightFooter = "printed: &D &T"

    End With



    I tried to include all of the properties that are covered in some of the posted XL4 solutions. There may be other properties that should be changed along with the headers/footers.
  2. TheScarfedOne's Avatar
    • |
    • permalink
    +1 for Notepad++ here, awesome for code writing I find too
  3. skunk's Avatar
    • |
    • permalink
    Vi, its the only way..................
  4. sparkeh's Avatar
    • |
    • permalink
    Ahhh Emacs, the king of text editors, is there nothing it can't do?
  5. kobusb's Avatar
    • |
    • permalink
    I set "Application.PrintCommunication = True" and that seems to solve the problem but I have not tested it extensively i.e. turning off print communication seems to create the problem.
  6. Pico's Avatar
    • |
    • permalink
    Quote Originally Posted by kobusb
    I experienced the same problem. Interestingly it does not occur if I slowly step through the code. If I quickly step through the code then it does not properly write the footers or headers.
    Intriguing! Thanks for sharing this.

    It makes me wonder whether there is a way to mimic this effect in the code. (My first thought was to try something like DoEvents, but that doesn't work.)
  7. kobusb's Avatar
    • |
    • permalink
    I experienced the same problem. Interestingly it does not occur if I slowly step through the code. If I quickly step through the code then it does not properly write the footers or headers.