Add Duplex command to Print Button (VBA)

[seanjon]

Good day.

A workbook I have has 22 worksheets. I want pages 1&2, 3&4, 5&6, etc. to print both sides. I have a color printer on the network that is not my default printer. I made a command button that will send the workbook to the color printer. I also want it to print it duplex.

This is what I have for the button so far. It works and sends the workbook to that printer.

Private Sub CommandButton1_Click()
Dim sCurrentPrinter As String
Const MyPrinter As String = "\\vm-print\MARS_BROTHERMFC-9460CDN on Ne06:"
sCurrentPrinter = Application.ActivePrinter
Application.ActivePrinter = MyPrinter
ActiveWorkbook.PrintOut From:=1, To:=22, Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Application.ActivePrinter = sCurrentPrinter
End Sub

Through searching, I know that there is a code to set it to duplex:

Sub SetPrinterToDuplex()
    SetPrinterDuplex "\\vm-print\MARS_BROTHERMFC-9460CDN on Ne06:", 2
End Sub

Where should I put this in the code for my command button, or is there something different I need to put in the sub?

Thanks for your time.

Sean

 

[Paul Ked]

You may be able to send control codes to the printer to tell it to print in duplex mode, but you would have to look through the printer documentation to see if this is possible.

Otherwise it is probably possible using the dark art of API calls, which I can only copy from the Wizards that write them!

 

[Paul Ked]

BTW

Sub SetPrinterToDuplex()
    SetPrinterDuplex "\\vm-print\MARS_BROTHERMFC-9460CDN on Ne06:", 2
End Sub

Won't work without the function SetPrinterDuplex

 

[seanjon]

BTW

Sub SetPrinterToDuplex()
    SetPrinterDuplex "\\vm-print\MARS_BROTHERMFC-9460CDN on Ne06:", 2
End Sub

Won't work without the function SetPrinterDuplex

I am not understanding what you mean by this. The code has setprinterduplex. What am I missing that you are telling me? Thanks

 

[Paul Ked]

A quick search turned up this function:

<code class="" style="box-sizing: border-box; font-size: inherit; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; padding: 0px; color: inherit; background-color: transparent; white-space: pre-wrap; border-radius: 0px;">   Public Function SetPrinterDuplex(ByVal sPrinterName As String, _
   ByVal nDuplexSetting As Long) As Boolean

      Dim hPrinter As Long
      Dim pd As PRINTER_DEFAULTS
      Dim pinfo As PRINTER_INFO_2
      Dim dm As DEVMODE
   
      Dim yDevModeData() As Byte
      Dim yPInfoMemory() As Byte
      Dim nBytesNeeded As Long
      Dim nRet As Long, nJunk As Long
   
      On Error GoTo cleanup
   
      If (nDuplexSetting < 1) Or (nDuplexSetting > 3) Then
         MsgBox "Error: dwDuplexSetting is incorrect."
         Exit Function
      End If
      
      pd.DesiredAccess = PRINTER_ALL_ACCESS
      nRet = OpenPrinter(sPrinterName, hPrinter, pd)
      If (nRet = 0) Or (hPrinter = 0) Then
         If Err.LastDllError = 5 Then
            MsgBox "Access denied -- See the article for more info."
         Else
            MsgBox "Cannot open the printer specified " & _
              "(make sure the printer name is correct)."
         End If
         Exit Function
      End If
   
      nRet = DocumentProperties(0, hPrinter, sPrinterName, 0, 0, 0)
      If (nRet < 0) Then
         MsgBox "Cannot get the size of the DEVMODE structure."
         GoTo cleanup
      End If
   
      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."
         GoTo cleanup
      End If
   
      Call CopyMemory(dm, yDevModeData(0), Len(dm))
   
      If Not CBool(dm.dmFields And DM_DUPLEX) Then
        MsgBox "You cannot modify the duplex flag for this printer " & _
               "because it does not support duplex or the driver " & _
               "does not support setting it from the Windows API."
         GoTo cleanup
      End If
   
      dm.dmDuplex = nDuplexSetting
      Call CopyMemory(yDevModeData(0), dm, Len(dm))
   
      nRet = DocumentProperties(0, hPrinter, sPrinterName, _
        VarPtr(yDevModeData(0)), VarPtr(yDevModeData(0)), _
        DM_IN_BUFFER Or DM_OUT_BUFFER)

      If (nRet < 0) Then
        MsgBox "Unable to set duplex setting to this printer."
        GoTo cleanup
      End If
   
      Call GetPrinter(hPrinter, 2, 0, 0, nBytesNeeded)
      If (nBytesNeeded = 0) Then GoTo cleanup
   
      ReDim yPInfoMemory(nBytesNeeded + 100) As Byte

      nRet = GetPrinter(hPrinter, 2, yPInfoMemory(0), nBytesNeeded, nJunk)
      If (nRet = 0) Then
         MsgBox "Unable to get shared printer settings."
         GoTo cleanup
      End If
   
      Call CopyMemory(pinfo, yPInfoMemory(0), Len(pinfo))
      pinfo.pDevmode = VarPtr(yDevModeData(0))
      pinfo.pSecurityDescriptor = 0
      Call CopyMemory(yPInfoMemory(0), pinfo, Len(pinfo))
   
      nRet = SetPrinter(hPrinter, 2, yPInfoMemory(0), 0)
      If (nRet = 0) Then
         MsgBox "Unable to set shared printer settings."
      End If
   
      SetPrinterDuplex = CBool(nRet)

cleanup:
      If (hPrinter <> 0) Then Call ClosePrinter(hPrinter)

   End Function
</code>

 

[Paul Ked]

The rest of the code and instructions (for Word, but it will be very similar for Excel) are here:

https://support.microsoft.com/en-us...rinting-for-microsoft-word-automation-clients