Printer Driver slowing Excel VBA Macro

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:
https://www.experts-exchange.com/questions/27035153/Microsoft-Excel-2007-Change-Default-Printer.html
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
    
    wks.Columns("E").AutoFit
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)
    Else
        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

Select allOpen in new window

See attached.

Cheers,

Dave
processMacroAfterChangingDefault.xls

Hi,

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?

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?

Dave

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.

Dave

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,
John

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.

Dave

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!