VBA in Excel 2016 - set printer settings in code

Question

I've scoured the internet but can't find an exact solution for this...I need to be able to set the color and duplex settings for printing from within my VBA code.

My intent is to use a VBA userform button to format and then print a table from my workbook. My abbreviated code is:

Application.PrintCommunication = False
With ws.PageSetup
    .PrintArea = first & ":" & last
    .PrintTitleRows = "$1:$1"
    .LeftHeader = "&9&D &T"
    .CenterHeader = "&A"
    .RightHeader = "&9Page &P of &N"
    .Orientation = xlLandscape
    .PaperSize = xlPaper11x17
    .LeftMargin = Application.InchesToPoints(0.25)
    .RightMargin = Application.InchesToPoints(0.25)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.25)
    .HeaderMargin = Application.InchesToPoints(0.3)
    .FooterMargin = Application.InchesToPoints(0.3)
End With
Application.PrintCommunication = True

ws.PrintOut Preview:=True, ActivePrinter:="BODHPM750"

This formats the page and opens the preview window fine, but it defaults to black-and-white one-sided printing (per the office's printer settings) unless I've manually changed it since opening the workbook. These settings are associated with the printer, not the workbook, so I can't change them through ws.PageSetup or within the preview window.

EDIT: I also realized that the ActivePrinter definition isn't working. The print preview is opening for the default printer set on my computer, which is a different one from the one I'm trying to call in my ws.PrintOut command.

Is there a way for me to set this to print in color and duplex within this button press? I found information on using a Printer object within Access and other Office programs, but there doesn't seem to be one for Excel.

For reference - I'm using VBA in Excel 2016 and the printer I'm trying to use is an HP Color LaserJet M750 PCL 6 on an office server, if that's relevant.

UPDATE: Color issue resolved.

Solution

Partial solution:

Color settings can be done through Page Setup

The ActivePrinter declaration in my PrintOut call was not working because I did not have the printer name exactly as the system wanted it. To find the printer name, I set the desired printer to my active printer through the Excel File>Print dialog and then output the name of the printer as follows:

To show in a message box:

MsgBox Application.ActivePrinter

To output to a cell so you can copy-paste the text:

Sheets("Sheet 1").Range("A1").Value = Application.ActivePrinter

As a workaround for changing the printer settings to duplex: Change the active printer and open the print dialog box before the PrintOut command: Application.ActivePrinter = "\SHAHQPS2\BODHPM750DTN1P on Ne09:" Application.Dialogs(xlDialogPrinterSetup).Show This brings up a dialog box where the printer settings can be changed.

NOT RESOLVED: Still looking for a solution to code changing printer setting to Duplex within the code instead of opening the Print Settings dialog box.

Current code:

'Set print settings
Application.PrintCommunication = False  'Freeze print communication for efficiency
With ws.PageSetup
    .PrintArea = first & ":" & last
    .PrintTitleRows = "$1:$1"
    .LeftHeader = "&9&D &T"
    .CenterHeader = "&A"
    .RightHeader = "&9Page &P of &N"
    .Orientation = xlLandscape
    .PaperSize = xlPaper11x17
    .LeftMargin = Application.InchesToPoints(0.25)
    .RightMargin = Application.InchesToPoints(0.25)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.25)
    .HeaderMargin = Application.InchesToPoints(0.3)
    .FooterMargin = Application.InchesToPoints(0.3)
End With
Application.PrintCommunication = True   'Restart print communication

'Set active printer to desired printer so print dialog box defaults to highlighting
'that printer; then open print dialog box so printer settings can be set to print
'duplex before the print preview comes up
'Note: could also use this to choose between printers
Application.ActivePrinter = "BODHPM750 on Ne09:"
Application.Dialogs(xlDialogPrinterSetup).Show

'Send to printer (preview first)
ws.PrintOut Preview:=True, ActivePrinter:="BODHPM750 on Ne09:"

Comments

[Our Man in Bananas]

you may find some help at PageSetup Object (Excel) and at Stack Overflow: Change printer properties in excel macro

[Our Man in Bananas]

also see here for more advice

[Our Man in Bananas]

additionally, if you want to use Excel4Macros, the help file for this can be downloaded from Microsoft here

[Kait]

Thanks for the reply! Those solutions only allow me to change PAGE specific settings...the same ones I could change when I click "Page Setup" in the Print Preview window. That could help with the color printing, but that actually would resolve itself if I could get it to send to the right printer, which defaults to color. My problem is changing PRINTER specific settings that can't be set within Page Setup -- why doesn't my ActivePrinter definition within the PrintOut call send to the specified printer? And how do I tell that printer to print duplex?

[Our Man in Bananas]

have a look at ExcelTip: Change the default printer using VBA in Microsoft Excel 2010 and Mr Excel (all thread)

[Kait]

Thanks! After looking through those, I'm definitely closer. I exported the active printer name to a cell...the exact name was different from the display name so I copy-pasted it in and now the printer declaration works. I also added "Application.Dialogs(xlDialogPrinterSetup).Show" before my PrintOut command so that at least allows me to go into the printer settings and set duplex before the print preview. So this is a viable workaround, though I'd still prefer to hard-code duplex printing in so I don't have to change the settings each time.

[Our Man in Bananas]

you could add that information with the code as a solution, answering your own question

[Our Man in Bananas]

have a look here and here for VBA Duplex printing

[Kait]

I may be misinterpreting, but I'm reading that the code in your last links (a) changes the default printer settings (b) requires the network permissions to do so. I'm sure I don't have the permissions and our IT guy definitely won't give them to me, and I also don't think I want to set the default settings to duplex...I just want the option to set it for the current printout (eventually I plan to have a checkbox so the duplex setting is in an IF statement). So very close, but not quite there. I may just have to be content with the printer settings dialog box. I really appreciate your help!