Solved

Printer Driver slowing Excel VBA Macro

Posted on 2012-04-10
9
1,182 Views
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:

http://www.experts-exchange.com/Microsoft/Applications/Q_26657924.html

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,
John
0
Comment
Question by:AmishTaxi
  • 5
  • 3
9 Comments
 
LVL 11

Expert Comment

by:jkpieterse
Comment Utility
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
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I've modified a recent solution of mine to adapt to your needs:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27035153.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

Open in new window


See attached.

Cheers,

Dave
processMacroAfterChangingDefault.xls
0
 

Author Comment

by:AmishTaxi
Comment Utility
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?
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
0
 

Author Comment

by:AmishTaxi
Comment Utility
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
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
0
 
LVL 41

Accepted Solution

by:
dlmille earned 300 total points
Comment Utility
>> 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.
http://office.microsoft.com/en-us/excel-help/create-or-delete-custom-views-HP005199633.aspx
http://chandoo.org/wp/2008/12/17/custom-views-excel/

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

Dave
0
 

Author Closing Comment

by:AmishTaxi
Comment Utility
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!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now