Printer Driver slowing Excel VBA Macro

Posted on 2012-04-10
Medium Priority
Last Modified: 2012-04-11
I have an Excel 2010  Web Query that is taking a fairly long time to format the data (Excel Macro runs slow).  After stepping through the process (ASP to Text file then a Excel Template that runs the VBA Macro) with timing routines, I could narrow it down to Excel page formatting.

Today, being a newbie on your site,  I located the following solution:


It runs much faster if I change "my" printer driver to something else, however, I was wondering if there is a VBA code solution for this?

My thinking is that turning off the default printer driver then back on, should work?

Thanks in advance,
Question by:AmishTaxi
  • 5
  • 3
LVL 11

Expert Comment

ID: 37831118
In Excel 2010 you can turn off communication with the printerdriver:

Application.PrintCommunication = False

(don't forget to turn back on at the end).
Also, turning off display of pagebreaks might help:

ActiveSheet.DIsplayPageBreaks = False

and of course Screenupdating

Application.Screenupdating = False
LVL 42

Expert Comment

ID: 37831121
I've modified a recent solution of mine to adapt to your needs:
This discusses how to set the default printer to another printer.

Hopefully, the following code is very straight forward so you can further adapt.

There's one sheet on the workbook with a button to enumerate your printers.  You can put an X to select one of them (it uses the first) as the "default printer" while your macro runs.  You can then hit the other button to run your macro. Once your macro has finished, the original default printer is set back to the default.

Once you know your printer names from what prints on your control panel, you could hard code that in and eliminate some of the code (e.g., the loop searching for the X) and just make a call to the set_DefaultPrinter() routine with the name of your printer.    See the code for runYourMacro2() which just does the steps where you've hard coded the temporary printer name.

This ASSUMES of course that you maintain your connections, or you select a local printer driver like an XPS or something.  You're not actually printing to the printer, just running a macro, right?

Here's the main code, the rest are support functions in other modules:
Option Explicit
'Source:Adapted from http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27035153.html

Sub getNetworkPrinters()
'Will enumerate printers on the "Control Panel" Sheet
Dim i As Integer, currDefaultPrinter As String
Dim wks As Worksheet

    Set wks = ThisWorkbook.Sheets("Control Panel")
    wks.Range("E6:E" & wks.Rows.Count).Clear
    'currDefaultPrinter = Get_DefaultPrinterName
    Call EnumeratePrinters4 'modified to load dynamic array, PrinterNames()
    For i = 0 To UBound(PrinterNames)
        wks.Range("E" & 6 + i).Value = PrinterNames(i)
    Next i
    wks.Range("E6", "E" & 6 + UBound(PrinterNames)).Interior.Color = vbYellow
    With wks.Range("E6", "E" & 6 + UBound(PrinterNames)).Offset(, -1)
        .Interior.Color = vbCyan
        .HorizontalAlignment = xlCenter
    End With
End Sub
Sub runYourMacro()
Dim wks As Worksheet
Dim rPrinter As Range
Dim rPrinters As Range
Dim originalPrinter As String
Dim bFound As Boolean

    originalPrinter = Get_DefaultPrinterName
    Set wks = ThisWorkbook.Sheets("Control Panel")
    Set rPrinters = wks.Range("D6", wks.Range("D" & wks.Rows.Count).End(xlUp))
    For Each rPrinter In rPrinters
        If UCase(rPrinter.Value) = "X" Then 'first, change default printer
            set_DefaultPrinter (rPrinter.Offset(, 1).Value)
            bFound = True
            Exit For
        End If
    Next rPrinter
    If bFound Then
        Call yourMacro
        'reset default printer back to original
        set_DefaultPrinter (originalPrinter)
        MsgBox "You did not mark an X in the control panel", vbCritical, "Aborting!!!"
    End If
End Sub
Sub runYourMacro2()
Dim originalPrinter As String

    originalPrinter = Get_DefaultPrinterName

    set_DefaultPrinter ("temporary default printer name")

    Call yourMacro
     set_DefaultPrinter (originalPrinter)
End Sub
Sub yourMacro()
    'your code goes here, to be run by runYourMacro() subroutine
End Sub

Open in new window

See attached.



Author Comment

ID: 37834942

I had a chance to research both suggestions above (thank you) and I wanted to add a few more comments on this topic.

My workstation is Excel 2010, but I need it to work on Excel 2000 - 2010 so the PrintCommunication command is out for me.  I do use screenupdating whenever I can, but that was a good try, thanks!

Also, DisplayPagebreaks is needed for some line drawing stuff that is happening in this report.

DisplayPagebreaks is also where I started to think a lot about changing the print driver since it will adversley effect how the lines are being placed.  Dave, I really liked your example, but I can not seem turn the Active Printing off like in the following example:

Dim originalPrinter As String

    ' Let's save the old printer:
    originalPrinter = Excel.ActivePrinter

    ' These lines Fails:  Excel.ActivePrinter = NULL   Excel.ActivePrinter = Nothing
    Excel.ActivePrinter = ""

    MsgBox ("Your printer defualt is now set to """ & Excel.ActivePrinter & """.")
    Excel.ActivePrinter = originalPrinter
    MsgBox ("Printer reset back to" & Excel.ActivePrinter)
So that is where I am at so far.... Ideas?
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

LVL 42

Expert Comment

ID: 37835023
Did my solution (or even using ActivePrinter) not work (speed up your macro) when you selected a local printer driver (e.g., an XPS printer)?

PS - I'm curious - how did you manually set your office to not have an active printer?

LVL 42

Expert Comment

ID: 37835092
Again, when you "turn off" your default printer driver, what are you doing?  Does it really create an inactive printer, or just it really just default to another printer in your list?

How are you doing it?  Through the control panel/printers screens, or via Excel?

I do not think you can set the ActivePrinter to a null value, and I do think when you change the default printer from outside of Excel, the operating system then selects another printer as your default printer (assuming you have more than one).

Pls advise.


Author Comment

ID: 37835425
Hi Dave,

I currently can not turn off the default printer yet (I don't think it is possible using anything windows).

An XPS Printer option will not work on my 2003 Office end user workstations.

I thought about selecting another printer automatically for my end-users, but if that printer driver does the same thing, then my application has failed to fix the problem (again)...

The only other idea for me is to ask our Admin team to update the network printer driver and see if that helps?

Thanks for helping,
LVL 42

Expert Comment

ID: 37835452
Ok for some reason I misread and thought you had changed the printer/turned it off...

Updating the printer driver certainly is an option.

I have a solution that can disable any active printer, but you'll need to think about it as I'd need to write the code to do it.

Here's what I did:

1.  went to the registry and exported my OS settings for all my printers.
2.  deleted those registry settings
3.  went to Excel and no printer was active - because there was no printer
4.  imported my export.reg file back into the registry
5.  went to Excel and I have printers again

Would this be a viable option?

I tried adding a printer to the registry that was bogus, then setting Excel active printer to that.  I added it to the Registry but no joy - the OS was too smart for that.

LVL 42

Accepted Solution

dlmille earned 1200 total points
ID: 37835462
>> Narrowed it down to page formatting.

So if you change or get rid of your page formatting, it runs quite well?  If so, perhaps there is an alternative solution that's much simpler...

You really need to read this tip on custom views.  Just create a custom view for the existing page formatting, then create one with no page formatting.  Have your macro switch to the custom view with no page formatting, run the query, then change back to the default page formatting.

Custom views are supported in the range of versions you say are important to your app: Excel 2000-2010.


Author Closing Comment

ID: 37835878
Yes, I am guessing that eliminating Page formatting should work as I already have a button to do it manually after the download...  The download is automated, so I need to make a few changes to test this sort of idea....

Wow, never thought of doing it using a custom view...  Tested it remotely and it "seems" to work (I'll test it on Friday and let you know if it does not!).

Ok, thanks so much for this one!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question