Printer Driver slowing Excel VBA Macro

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,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan Karel PieterseExcel and VBA ExpertCommented:
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
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

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.


AmishTaxiAuthor Commented:

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?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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?

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.

AmishTaxiAuthor Commented:
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,
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.

>> 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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AmishTaxiAuthor Commented:
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.