VBA : changing the active printer takes a looong time

[Daniel Dion]

I have a file with more than 600 sheets and I am using a macro to change the active printer : application.ActivePrinter="\\xxx on Ne01:". This single line of code may take up to 90 seconds to execute. Changing the active printer manually takes a second or two. Is there a way to speed up the changing of the active printer with vba??

Daniel

[JieJenn]

If you already know your printer name then a simple VBA script as below should do the job

Please Login or Register  to view this content.

[Daniel Dion]

Thank you.

I am using application.activeprinter to change the printer. I can change the printer but it takes a long time to execute when a file with more than 600 sheets is open, it takes less then 1 second when this file is close. I have the same issue with your procedure: it takes a long time (1 to 2 minutes) to run. Changing the printer manually takes a second or two.

[JieJenn]

The code is to set the default printer of for all your desktop applications. Usually the speed is depending on your 1) network speed and 2) number of devices are available. If you have many printers listed on your network, it could take a bit of time.

[Daniel Dion]

Understand but your procedure takes approx.10 seconds to execute but will take more than 1 minute if a file containing 600 sheets is open.

[JieJenn]

For some reason I was under the impression that you are printing multiple files. If you are printing just a single file from a separate printer than the default, then your method will work the best. I was giving a solution thinking you are printing multiple files.

Unfortunately, the printer object when executed does take a bit of time.

[Daniel Dion]

OK thank you

[Leith Ross]

Hello Daniel Dion,

Try suspending workbook events and all calculations before changing the printer like this...

Please Login or Register  to view this content.

[Daniel Dion]

I tried suspending workbook events with the same results, non change in the execution time.
Thank you for your suggestion.

[Daniel Dion]

I tried it no change on th execution time.

[Marc L]

Hi !

As it's just an Excel drawback : the more sheets, the longer …