View RSS Feed

It looks like you are trying to write a blog...

Excel 2010 - Printer Fails to Communicate

Rating: 3 votes, 4.33 average.
by , 5th May 2011 at 03:52 PM (32974 Views)
Long term users of Excel VBA will be familiar with slow processing of page set up if code like the following is used:

With ActiveWorksheet.PageSetup
.CenterHeader = "Title of the Worksheet"
.LeftFooter = "Today's Date"
.RightFooter = "Page &P of &N"
.PrintArea = "$A$1:$H$28"
End With


The problem with the code is that, despite the use of the With statement, each line requires a call to the printer driver, rather than the whole statement being sent once. The most common solution is to fall back on the Page.SetUp Excel 4 macro. An example of the kind of code that a programmer might use can be found here: McGimpsey & Associates : Excel : Bypassing Slow VBA PageSetup.

Changes to Excel 2010 were intended to remove the need to fall back on legacy code by switching on and off the printer communication (additions to code in blue):

Application.PrintCommunication = False

With ActiveWorksheeet.PageSetup
.CenterHeader = "Title of the Worksheet"
.LeftFooter = "Today's Date"
.RightFooter = "Page &P of &N"
.PrintArea = "$A$1:$H$28"
End With


Application.PrintCommunication = True

Further information can be found here: Microsoft Excel - Migrating Excel 4 Macros to VBA.

Unfortunately, it doesn't work as intended! Although I present the problem in terms of the footer, the same applies to the header.

The footer has three components: Left, Center and Right. If these have not yet been set the macro works as intended. However, if any of these have been previously assigned to, only the last footer section listed in the macro will have its text changed. That is, whichever is the last of LeftFooter, CenterFooter and RightFooter as listed in code order is changed and the others are left untouched.

Additionally, using the familiar "Page &P of &N" gets cut off if more than one section of the footer is assigned to.

It appears others have experienced the same problem:



In each case the advice is not to use PrintCommunication!

This is a real pity as apart from problems with (re-) setting the footers and headers, it works exactly as intended. Nonetheless, I am still using Excel 4 macros in version 14 >.<

Updated 19th October 2011 at 01:30 PM by Pico

Categories
Office Agonistes

Comments

  1. kobusb's Avatar
    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.
  2. Pico's Avatar
    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.)
  3. kobusb's Avatar
    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.
  4. simchuck's Avatar
    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.

Trackbacks

Total Trackbacks 0
Trackback URL: