VBA in Excel 2016 - set printer settings in code

2
votes

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.

2
excelvbaprinting
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 - Our Man in Bananas
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? - 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. - Kait

2 Answers

2
votes

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