Excel VBA printer API, set colour and duplex

Question

here's my problem.

I am trying to access the printer and change the colour and duplex settings. So far the code I have allows me to change the user preferences of the networked printer. But I have the following two problems below.

1) The codes set's the printer to either simplex or duplex as intended, however is does not set the colour preference correctly!

2) Excel is not automatically picking up the new settings, I still have to go in and manually click the reset button for the new changes to take affect.

Here is the code I am using:

Private Type PRINTER_INFO_9
pDevmode As Long ' Pointer to DEVMODE
End Type

Private Type DEVMODE
    dmDeviceName As String * 32
    dmSpecVersion As Integer
    dmDriverVersion As Integer
    dmSize As Integer
    dmDriverExtra As Integer
    dmFields As Long
    dmOrientation As Integer
    dmPaperSize As Integer
    dmPaperLength As Integer
    dmPaperWidth As Integer
    dmScale As Integer
    dmCopies As Integer
    dmDefaultSource As Integer
    dmPrintQuality As Integer
    dmColor As Integer
    dmDuplex As Integer
    dmYResolution As Integer
    dmTTOption As Integer
    dmCollate As Integer
    dmFormName As String * 32
    dmUnusedPadding As Integer
    dmBitsPerPel As Integer
    dmPelsWidth As Long
    dmPelsHeight As Long
    dmDisplayFlags As Long
    dmDisplayFrequency As Long
    dmICMMethod As Long
    dmICMIntent As Long
    dmMediaType As Long
    dmDitherType As Long
    dmReserved1 As Long
    dmReserved2 As Long
End Type

Private Declare Function OpenPrinter Lib "winspool.drv" Alias _
"OpenPrinterA" (ByVal pPrinterName As String, phPrinter As Long, _
pDefault As Any) As Long

Private Declare Function GetPrinter Lib "winspool.drv" Alias _
"GetPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, _
buffer As Long, ByVal pbSize As Long, pbSizeNeeded As Long) As Long

Private Declare Function SetPrinter Lib "winspool.drv" Alias _
"SetPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, _
pPrinter As Any, ByVal Command As Long) As Long

Private Declare Function DocumentProperties Lib "winspool.drv" _
Alias "DocumentPropertiesA" (ByVal hwnd As Long, _
ByVal hPrinter As Long, ByVal pDeviceName As String, _
ByVal pDevModeOutput As Long, ByVal pDevModeInput As Long, _
ByVal fMode As Long) As Long

Private Declare Function ClosePrinter Lib "winspool.drv" _
(ByVal hPrinter As Long) As Long

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
(pDest As Any, pSource As Any, ByVal cbLength As Long)

Private Const DM_IN_BUFFER = 8
Private Const DM_OUT_BUFFER = 2

Private Sub CommandButton1_Click()
Dim sPrinterName As String
Dim my_printer_address As String
Dim hPrinter As Long
Dim Pinfo9 As PRINTER_INFO_9
Dim dm As DEVMODE
Dim yDevModeData() As Byte
Dim nRet As Long

my_printer_address = Application.ActivePrinter

'slice string for printer name (minus port name)
sPrinterName = Left(my_printer_address, InStr(my_printer_address, " on ") - 1)

'Open Printer
nRet = OpenPrinter(sPrinterName, hPrinter, ByVal 0&)

'Get the size of the DEVMODE structure
nRet = DocumentProperties(0, hPrinter, sPrinterName, 0, 0, 0)
If (nRet < 0) Then MsgBox "Cannot get the size of the DEVMODE structure.": Exit Sub

'Get DEVMODE Structure
ReDim yDevModeData(nRet + 100) As Byte
nRet = DocumentProperties(0, hPrinter, sPrinterName, VarPtr(yDevModeData(0)), 0, DM_OUT_BUFFER)
If (nRet < 0) Then
    MsgBox "Cannot get the DEVMODE structure."
    Exit Sub
End If

'Copy the DEVMODE structure
Call CopyMemory(dm, yDevModeData(0), Len(dm))

'Change DEVMODE Stucture as required
dm.dmColor = 1  ' 1 = colour, 2 = b/w
dm.dmDuplex = 2 ' 1 = simplex, 2 = duplex

'Replace the DEVMODE structure
Call CopyMemory(yDevModeData(0), dm, Len(dm))

'Verify DEVMODE Stucture
nRet = DocumentProperties(0, hPrinter, sPrinterName, VarPtr(yDevModeData(0)), VarPtr(yDevModeData(0)), DM_IN_BUFFER Or DM_OUT_BUFFER)

Pinfo9.pDevmode = VarPtr(yDevModeData(0))

'Set DEVMODE Stucture with any changes made
nRet = SetPrinter(hPrinter, 9, Pinfo9, 0)
If (nRet <= 0) Then MsgBox "Cannot set the DEVMODE structure.": Exit Sub

'Close the Printer
nRet = ClosePrinter(hPrinter)

End Sub

Any help you can provide will be much appreciated!! I have been hitting my head against a wall with this for weeks now!

Answer 1

After some extensive research, I have found the answer I was looking for. I have posted it here, in case anyone has a similar situation.

The main issue I was having was getting excel to accept the new changes with closing the workbook or having to go into the print preferences and click reset.

The solution I came up with was to temporarily set the active printer to another printer then set it back to the printer the settings were changed on, this forces Excel to pick up the new settings.

Here are the Public Types, Functions and Constants:

Public Type PRINTER_INFO_9
    pDevmode As Long '''' POINTER TO DEVMODE
End Type

Public Type DEVMODE
    dmDeviceName As String * 32
    dmSpecVersion As Integer: dmDriverVersion As Integer
    dmSize As Integer
    dmDriverExtra As Integer
    dmFields As Long
    dmOrientation As Integer
    dmPaperSize As Integer
    dmPaperLength As Integer
    dmPaperWidth As Integer
    dmScale As Integer
    dmCopies As Integer
    dmDefaultSource As Integer
    dmPrintQuality As Integer
    dmColor As Integer
    dmDuplex As Integer
    dmYResolution As Integer
    dmTTOption As Integer
    dmCollate As Integer
    dmFormName As String * 32
    dmUnusedPadding As Integer
    dmBitsPerPel As Integer
    dmPelsWidth As Long
    dmPelsHeight As Long
    dmDisplayFlags As Long
    dmDisplayFrequency As Long
    dmICMMethod As Long
    dmICMIntent As Long
    dmMediaType As Long
    dmDitherType As Long
    dmReserved1 As Long
    dmReserved2 As Long
End Type

Public Declare Function OpenPrinter Lib "winspool.drv" Alias "OpenPrinterA" (ByVal pPrinterName As String, phPrinter As Long, pDefault As Any) As Long
Public Declare Function GetPrinter Lib "winspool.drv" Alias "GetPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, buffer As Long, ByVal pbSize As Long, pbSizeNeeded As Long) As Long
Public Declare Function SetPrinter Lib "winspool.drv" Alias "SetPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, pPrinter As Any, ByVal Command As Long) As Long
Public Declare Function DocumentProperties Lib "winspool.drv" Alias "DocumentPropertiesA" (ByVal hWnd As Long, ByVal hPrinter As Long, ByVal pDeviceName As String, _
                                                                                            ByVal pDevModeOutput As Long, ByVal pDevModeInput As Long, _
                                                                                            ByVal fMode As Long) As Long
Public Declare Function ClosePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (pDest As Any, pSource As Any, ByVal cbLength As Long)
Public Const DM_IN_BUFFER = 8
Public Const DM_OUT_BUFFER = 2

This is the routine i am using to set the new values:

Public Sub SetPrinterProperty(ByVal sPrinterName As String, ByVal iPropertyType As Long)
Dim PrinterName, sPrinter, sDefaultPrinter As String
Dim Pinfo9 As PRINTER_INFO_9
Dim hPrinter, nRet As Long
Dim yDevModeData() As Byte
Dim dm As DEVMODE

'''' STROE THE CURRENT DEFAULT PRINTER
sDefaultPrinter = sPrinterName

'''' USE THE FULL PRINTER ADDRESS TO GET THE ADDRESS AND NAME MINUS THE PORT NAME
PrinterName = Left(sDefaultPrinter, InStr(sDefaultPrinter, " on ") - 1)

'''' OPEN THE PRINTER
nRet = OpenPrinter(PrinterName, hPrinter, ByVal 0&)

'''' GET THE SIZE OF THE CURRENT DEVMODE STRUCTURE
nRet = DocumentProperties(0, hPrinter, PrinterName, 0, 0, 0)
If (nRet < 0) Then MsgBox "Cannot get the size of the DEVMODE structure.": Exit Sub

'''' GET THE CURRENT DEVMODE STRUCTURE
ReDim yDevModeData(nRet + 100) As Byte
nRet = DocumentProperties(0, hPrinter, PrinterName, VarPtr(yDevModeData(0)), 0, DM_OUT_BUFFER)
If (nRet < 0) Then MsgBox "Cannot get the DEVMODE structure.": Exit Sub

'''' COPY THE CURRENT DEVMODE STRUCTURE
Call CopyMemory(dm, yDevModeData(0), Len(dm))

'''' CHANGE THE DEVMODE STRUCTURE TO REQUIRED
dm.dmDuplex = iPropertyType ' 1 = simplex, 2 = duplex

'''' REPLACE THE CURRENT DEVMODE STRUCTURE WITH THE NEWLEY EDITED
Call CopyMemory(yDevModeData(0), dm, Len(dm))

'''' VERIFY THE NEW DEVMODE STRUCTURE
nRet = DocumentProperties(0, hPrinter, PrinterName, VarPtr(yDevModeData(0)), VarPtr(yDevModeData(0)), DM_IN_BUFFER Or DM_OUT_BUFFER)

Pinfo9.pDevmode = VarPtr(yDevModeData(0))

'''' SET THE DEMODE STRUCTURE WITH ANY CHANGES MADE
nRet = SetPrinter(hPrinter, 9, Pinfo9, 0)
If (nRet <= 0) Then MsgBox "Cannot set the DEVMODE structure.": Exit Sub

'''' CLOSE THE PRINTER
nRet = ClosePrinter(hPrinter)

'''' GET THE FULL PRINTER NAME FOR THE CUTE PDF WRITER
sPrinter = GetPrinterFullName("CutePDF")

'''' CHECK TO MAKE SURE THE CUTEPDF WAS FOUND
If sPrinter <> vbNullString Then
'''' THIS FORCES EXCEL TO ACCEPT THE NEW CHANGES THAT HAVE BEEN MADE TO THE PRINTER SETTINGS
    '''' SET THE ACTIVE PRINTER TEMPERARILLY TO THE CUTE PDF WRITER
    Application.ActivePrinter = sPrinter
    '''' SET THE PRINTER BACK TO THE DEFAULY FOLLOW ME.
    Application.ActivePrinter = sDefaultPrinter
End If
End Sub

I then call either of these two subs to set set preferences:

Public Sub SetDuplex(ByVal sPrinterName As String, iDuplex As Long)
   SetPrinterProperty sPrinterName, iDuplex
End Sub
Public Sub SetSimplex(ByVal sPrinterName As String, iDuplex As Long)
   SetPrinterProperty sPrinterName, iDuplex
End Sub

Answer 2

Awesome. Thank you for this fix. Our office recently switched to Windows 10 and Office 16 and my old duplex code no longer worked for printing out worksheets in duplex mode. Your code is incredibly complicated, but it works (for reasons beyond my understanding as a novice programmer) and saves a lot of paper from being wasted. Thank you very much. I did notice one thing about your function that needs to be addressed. There is a call to another function you did not provide.

sPrinter = GetPrinterFullName("CutePDF")

Coincidentally I happened to have the function GetPrinterFullName() in another module so it was running but not returning the full name of "CutePDF". That was because "CutePDF" does not exist on my computer. So I simply went to settings, set the default to "Microsoft Print to PDF" and then did a small test routine (below) to get the full name of the active default printer:

sub getActivePrinterFullAddress()
    debug.print application.activeprinter
end sub

This returned "Microsoft Print to PDF on Ne03:" So any user could send the full name of any second printer to your function by adding a 3rd variable and avoid the call to GetPrinterFullName(), or they can hard code the name into your function like I did to avoid the call. Or they can add the following function to the module: (I've been to 30 different sites this morning to find a solution, and yours is the one that works. But the credits for the following function are inside the function below. It is not my code. I think it is credited to Frans Bus)

Public Function GetPrinterFullName(Printer As String) As String

' This function returns the full name of the first printerdevice that
   matches Printer.
' Full name is like "PDFCreator on Ne01:" for a English Windows and like
' "PDFCreator sur Ne01:" for French.
' Created: Frans Bus, 2015. See http://pixcels.nl/set-activeprinter-excel
' see http://blogs.msdn.com/b/alejacma/archive/2008/04/11/how-to-read-a-
  registry-key-and-its-values.aspx
' see http://www.experts-exchange.com/Software/Microsoft_Applications/Q_27566782.html

Const HKEY_CURRENT_USER = &H80000001
Dim regobj As Object
Dim aTypes As Variant
Dim aDevices As Variant
Dim vDevice As Variant
Dim sValue As String
Dim v As Variant
Dim sLocaleOn As String

' get locale "on" from current activeprinter
v = Split(Application.ActivePrinter, Space(1))
sLocaleOn = Space(1) & CStr(v(UBound(v) - 1)) & Space(1)

' connect to WMI registry provider on current machine with current user
Set regobj = GetObject("WINMGMTS:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")

' get the Devices from the registry
regobj.EnumValues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", aDevices, aTypes

' find Printer and create full name
For Each vDevice In aDevices
    ' get port of device
    regobj.GetStringValue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", vDevice, sValue
    ' select device
    If Left(vDevice, Len(Printer)) = Printer Then ' match!
        ' create localized printername
        GetPrinterFullName = vDevice & sLocaleOn & Split(sValue, ",")(1)
        Exit Function
    End If
Next

' at this point no match found
GetPrinterFullName = vbNullString

End Function