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.

Answer 1

Sub A4DocuPrinter()

 Application.Dialogs(xlDialogPrinterSetup).Show

 Worksheets("Printer_Config").Range("D9").Value = Application.ActivePrinter 

End Sub

Sub A4DocuPrinter()

 Application.Dialogs(xlDialogPrinterSetup).Show

 Worksheets("Printer_Config").Range("D9").Value = Application.ActivePrinter

End Sub


Sub a4_Print()

 Application.ActivePrinter = Worksheets("Printer_Config").Range("D9").Value

 ActiveWindow.SelectedSheets.PrintOut Copies:=1

End Sub


Sub A4_Printer_Path()


 Dim p

 Dim i

  i = Worksheets("Printer_Config").Range("D9").Value
 
 '~Ne09:
   
   For p = 0 To 9

   On Error Resume Next

   Application.ActivePrinter = Left(i, Len(i) - 3) & "0" & p & Right(i, 1)

   Next
   

 '~Ne19:

   For p = 0 To 9

   On Error Resume Next

   Application.ActivePrinter = Left(i, Len(i) - 3) & "1" & p & Right(i, 1)

   Next


   Worksheets("Printer_Config").Range("D9").Value = Application.ActivePrinter

End Sub


Sub Docu_Print()

 If Worksheets("Printer_Config").Range("D9").Value = "" Then
 

 MsgBox ("Printer Path ??")

 A4DocuPrinter


 Else
 
 
  On Error GoTo PrintError:
  
 Application.ActivePrinter = Worksheets("Printer_Config").Range("D9").Value
  
 a4_Print

End If

Exit Sub

 PrintError:

 A4_Printer_Path

 a4_Print

End Sub


Sub A4_Preview()

 Application.ActivePrinter = Worksheets("Printer_Config").Range("D9").Value

 ActiveWindow.SelectedSheets.PrintPreview

End Sub

Answer 2

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:"