VBA Print Reports on both side of paper

[Peter Davison]

Hi All,
I have this VBA Code to print a report and wanted to know if I could add code that would allow the user to choose if they wanted to print on both sides (I think I have achieved this part with my code in bold currently turned into a comment).
However, I'm not sure how to add code at the end to make the printer action the choice?
If anyone has any ideas that would be great.

Sub SetPrintSettingsAndPrintAddNewStore()
Dim ws As Worksheet
Dim lastRow As Long

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Add New Store")

' Find the last row with data in column B
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

' Prompt user for print mode
'printBothSides = MsgBox("Do you want to print on both sides?", vbYesNo) = vbYes

' Set the print range
'If printBothSides Then
ws.PageSetup.PrintArea = "$B$1:$L$" & lastRow
'Else
'ws.PageSetup.PrintArea = "$B$1:$L$" & lastRow * 2 ' Double the last row for duplex printing
'End If

' Set the orientation to Landscape
ws.PageSetup.Orientation = xlLandscape

' Set titles row
ws.PageSetup.PrintTitleRows = "$11:$11"

' Set margins
With ws.PageSetup
.LeftMargin = Application.InchesToPoints(0.1)
.RightMargin = Application.InchesToPoints(0.1)
.TopMargin = Application.InchesToPoints(0.1)
.BottomMargin = Application.InchesToPoints(0.1)
End With

' Print the document
ws.PrintOut
End Sub

 

[John_w]

Add a Windows printer which can print double-sided and use this part of the code from my post here:

Print sheets based on checkbox

I want to be able to print specific sheets within a workbook based on whether certain checkboxes are checked. I have a workbook with 10 sheets that I want to print between 1 and all of them based on whether a box is checked. I have created a selector sheet with 10 checkboxes (one for each...

www.mrexcel.com

    Dim currentPrinter As String
    Dim WindowsPrinterName As String
    Dim NEPrinterName

    currentPrinter = Application.ActivePrinter
    
    WindowsPrinterName = "Your double-sided printer name"

    'Get the full name of the printer, including its NExx: network port, and make it the active printer
    
    NEPrinterName = FindPrinter(WindowsPrinterName)
    Application.ActivePrinter = NEPrinterName

Plus the FindPrinter function.

NB your print both sides logic is the wrong way round; it should be:

'If printBothSides Then
        ws.PageSetup.PrintArea = "$B$1:$L$" & lastRow * 2   'double-sided
    'Else
        'ws.PageSetup.PrintArea = "$B$1:$L$" & lastRow 
'End If

 

[Peter Davison]

Thank you for coming back to me.
Do I put the first part of the VBA code above (Find Printer) just before the 'Print Document in my code?
or could you add it in the correct place in my code above please, just so I get it right.
Where it says "Your Printer Name" do I put for example - Canon Pixma MG4250?
Appreciate your help

 

[Burrgogi]

Where it says "Your Printer Name" do I put for example - Canon Pixma MG4250?
Appreciate your help

You need to type in exactly as it appears in your Windows printer applet. I don't know which version of Windows you are using. Easiest way would be to just type in "Devices & Printers" in the Start search menu.

 

[Peter Davison]

Did you mean the exact wording as shown on the printers & Scanners sheet below

 

[Peter Davison]

I am on Windows 10

 

[John_w]

Did you mean the exact wording as shown on the printers & Scanners sheet below

Yes.

You would have 2 Windows printers with different names on the 'Printers & scanners' dialogue, for the same physical printer. You set the print settings for one of the named printers to double-sided printing and the print settings for the other named printer to single-sided printing. Assuming your Canon MG4200 printer is capable of duplex printing you might name the printers like this:

Canon MG4200 Single-Sided

Canon MG4200 Double-Sided

Those are the printer names exactly as they appear on the 'Printers & scanners' dialogue.

Then the following macro prints the sheet range on the chosen printer:

Public Sub SetPrintSettingsAndPrintAddNewStore()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim currentPrinter As String
    Dim WindowsPrinterName As String
    Dim NEPrinterName As String

    'Save current printer
    currentPrinter = Application.ActivePrinter
    Debug.Print currentPrinter
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Add New Store")
    
    ' Find the last row with data in column B
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Prompt user for print mode
    
    With ws
    
        .PageSetup.PrintArea = "$B$1:$L$" & lastRow
    
        ' Set the orientation to Landscape
        .PageSetup.Orientation = xlLandscape
        
        ' Set titles row
        .PageSetup.PrintTitleRows = "$11:$11"
        
        ' Set margins
        With .PageSetup
            .LeftMargin = Application.InchesToPoints(0.1)
            .RightMargin = Application.InchesToPoints(0.1)
            .TopMargin = Application.InchesToPoints(0.1)
            .BottomMargin = Application.InchesToPoints(0.1)
        End With
    
        ' Print the document
        If MsgBox("Do you want to print on both sides?", vbYesNo) = vbYes Then
            WindowsPrinterName = "Canon MG4200 Double-Sided"
        Else
            WindowsPrinterName = "Canon MG4200 Single-Sided"
        End If
        
        'Get the full name of the printer, including its NExx: network port, and print the sheet on that printer
    
        NEPrinterName = FindPrinter(WindowsPrinterName)
        If NEPrinterName <> "" Then
            .PrintOut ActivePrinter:=NEPrinterName
        Else
            MsgBox "Printer not found: """ & WindowsPrinterName & """", vbExclamation
        End If
        
    End With
        
    'Restore current printer
    Application.ActivePrinter = currentPrinter
    
End Sub


'Written: November 28, 2009
'Author:  Leith Ross
'Summary: Finds a printer by name and returns the printer name and port number.

Public Function FindPrinter(ByVal printerName As String) As String

    'This works with Windows 2000 and up
    
    Dim Arr As Variant
    Dim Device As Variant
    Dim Devices As Variant
    Dim printer As String
    Dim RegObj As Object
    Dim RegValue As String
    Const HKEY_CURRENT_USER = &H80000001
    
    Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    RegObj.enumValues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Devices, Arr
    
    For Each Device In Devices
        RegObj.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Device, RegValue
        'Debug.Print Device
        printer = Device & " on " & Split(RegValue, ",")(1)
        'If InStr(1, Printer, PrinterName, vbTextCompare) > 0 Then  'original code
        If StrComp(Device, printerName, vbTextCompare) = 0 Then
            FindPrinter = printer
            Exit Function
        End If
    Next
      
End Function

 

[Peter Davison]

Thank you so much for your assistance.
Do I need to change the printer current name in the section below?
Then do I have to do find a printer again and add the Canon MG4200 again?

 

[John_w]

Do I need to change the printer current name in the section below?

Yes.

Then do I have to do find a printer again and add the Canon MG4200 again?

Yes, and give it a different name and set it to double-sided printing. This is usually set by clicking the Preferences... button shown and/or in Advanced tab -> Printing Defaults...

 

[Peter Davison]

Very helpful
Regards