Controlling print settings using Excel macro

Hello.

I currently have a workbook with several worksheets. Having run the first part of the macro, only some of the worksheets contain data.

The second part of the macro determines which worksheets have data, and then automatically prints the relevant pages. My problem is that our network settings have changed in two ways:
1) It prevents us from permenanlty selecting 'Hold Print' from the preferences.
2) It automatically selects duplex printing.

Each of the sheets printed need to be held and without duplex. Before I print, it is possible to manually change the settings but they work only for the first worksheet, each one thereafter is printed duplex etc. Is there a macro that allows Excel to take control of the print settings throughout the entire print run?

Thanks

Darren

Hello Darren,

I can help you with the Duplex settings, but I am unfamiliar with the "Hold Print" setting. As you can see from the code below, this is a non trivial operation requiring the Windows API. This code contains 3 public macros for setting the Active Printer's Simplex/Duplex Mode.

API and Macro Code

Please Login or Register  to view this content.

Adding the API and Macro Code
1. Copy the all the code above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

Apologies for taking so long to reply - thank you very much for the information you have posted, it must have taken a long time to put it together.

Kind Regards

Darren

Hello Darren,

Did the code solve your problem?

Sincerely,
Leith Ross

I'm getting the following error when I try to use this macro:

Compile error: Ambiguous name detected: ClosePrinter.

Any help would be greatly appreciated!

Hello gabrieldavisjones, and welcome to the forum.

Unfortunately you have inadvertently broken two of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at the top of the page and read Rule #3.
Thanks.