help with printer VBA code

[Jagat Pavasia]

dear sir,

I have 3 printers named in my windows 10 PC:
1) Samsung SCX-3200
2) Brother 7535dw
3) Brother QL-800

my default printer is Samsung SCX-3200, generally I print in this printer.

But now I want to Print my excel workbook sheet in selected area from A11:E19 with "Brother QL-800" the label Printer.

and then after printing my default printer should be returned to originally "Samsung SCX-3200".

I want to add macro in my sheet. (with Print Button)

I have attached screenshot for more details.



thank you .......

please help me

 

[Jaafar Tribak]

I want something like

Sub PrintAddress()
Set mynetwork = CreateObject("WScript.network")
mynetwork.SetDefaultPrinter "Brother QL-800"
Sheets("Sheet7").Range("A11:E19").PrintOut
mynetwork.SetDefaultPrinter "Samsung SCX-3200 Series"
End Sub

Doesn't that work for you ? Are you getting some error ?

I tested your code and It worked ok for me.

 

[Jagat Pavasia]

I have done my job by below VBA code successfully...


Sub Address()
Set mynetwork = CreateObject("WScript.network")
mynetwork.SetDefaultPrinter "Brother QL-800"
Range("A11:E19").PrintOut
mynetwork.SetDefaultPrinter "Brother DCP-B7535DW series"

End Sub

 

[Jagat Pavasia]

Doesn't that work for you ? Are you getting some error ?

I tested your code and It worked ok for me.

dear sir, it working as it should.

But when I print manually in my Printer "Samsung SCX-3200 Series" in any sheets on same workbook and then after,

I want to Print my selected range ("A11:E19") by clicking macro button, it did not print on "Brother QL-800", the print come out from "Samsung SCX-3200 Series".

I want that my Macro button always print on my label printer "Brother QL-800", even I print manually from any printer from any sheet of workbook.

I have entered VBA code in my Sheet7, see below, what should I do in VBA macro?

My Currant macro is Below.


( I have total sheet is Seven in this workbook)

Sub Address()
Set mynetwork = CreateObject("WScript.network")
mynetwork.SetDefaultPrinter "Brother QL-800"
Sheets("Print").Range("A11:F19").PrintOut
mynetwork.SetDefaultPrinter "Samsung SCX-3200 Series"
End Sub

 

[NdNoviceHlp]

Hi Jagat. Please remember that members are volunteering their time to help you. Jaafar's code does seem to need a bit of a logic tweek and the 64 bit ready code needs a bit of revision. Call the code with the original test sub. HTH. Dave
Module code....

Option Explicit

#If VBA7 And Win64 Then
    Private Declare PtrSafe Function GetDefaultPrinter Lib "winspool.drv" Alias "GetDefaultPrinterA" (ByVal sPrinterName As String, lPrinterNameBufferSize As Long) As Long
    Private Declare PtrSafe Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long
#Else
    Private Declare Function GetDefaultPrinter Lib "winspool.drv" Alias "GetDefaultPrinterA" (ByVal sPrinterName As String, lPrinterNameBufferSize As Long) As Long
    Private Declare Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long
#End If


Sub PrintToSpecificPrinter(ByVal ObjectToPrint As Object, ByVal PrinterName As String)
Dim sCurrentPrinter As String
sCurrentPrinter = GetActivePrinter
If sCurrentPrinter <> PrinterName Then
If SetDefaultPrinter(PrinterName) Then
ObjectToPrint.PrintOut
'MsgBox PrinterName
End If
Else
ObjectToPrint.PrintOut
'MsgBox sCurrentPrinter
End If
End Sub

Private Function GetActivePrinter() As String
    Dim sBuffer As String * 128, lBuffSize As Long
    lBuffSize = 128
    If GetDefaultPrinter(sBuffer, lBuffSize) Then
        GetActivePrinter = Left(sBuffer, lBuffSize - 1)
    End If
End Function

ps. I'm guessing that Jaafar's code is actually correct and that I maybe wrong again. I just thought that I would post as I just spent several days adjusting my 32 bit print code to 64 bit code. I have 32 bit application(s) both with and without VBA7 so you need to specify "#If VBA7 And Win64 Then"

 

[Jagat Pavasia]

Hi Jagat. Please remember that members are volunteering their time to help you. Jaafar's code does seem to need a bit of a logic tweek and the 64 bit ready code needs a bit of revision. Call the code with the original test sub. HTH. Dave
Module code....

Option Explicit

#If VBA7 And Win64 Then
    Private Declare PtrSafe Function GetDefaultPrinter Lib "winspool.drv" Alias "GetDefaultPrinterA" (ByVal sPrinterName As String, lPrinterNameBufferSize As Long) As Long
    Private Declare PtrSafe Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long
#Else
    Private Declare Function GetDefaultPrinter Lib "winspool.drv" Alias "GetDefaultPrinterA" (ByVal sPrinterName As String, lPrinterNameBufferSize As Long) As Long
    Private Declare Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long
#End If


Sub PrintToSpecificPrinter(ByVal ObjectToPrint As Object, ByVal PrinterName As String)
Dim sCurrentPrinter As String
sCurrentPrinter = GetActivePrinter
If sCurrentPrinter <> PrinterName Then
If SetDefaultPrinter(PrinterName) Then
ObjectToPrint.PrintOut
'MsgBox PrinterName
End If
Else
ObjectToPrint.PrintOut
'MsgBox sCurrentPrinter
End If
End Sub

Private Function GetActivePrinter() As String
    Dim sBuffer As String * 128, lBuffSize As Long
    lBuffSize = 128
    If GetDefaultPrinter(sBuffer, lBuffSize) Then
        GetActivePrinter = Left(sBuffer, lBuffSize - 1)
    End If
End Function

ps. I'm guessing that Jaafar's code is actually correct and that I maybe wrong again. I just thought that I would post as I just spent several days adjusting my 32 bit print code to 64 bit code. I have 32 bit application(s) both with and without VBA7 so you need to specify "#If VBA7 And Win64 Then"

I can not understand that how and where i put this VBA code,
please suggest me in little bit detail, I am now in VBA (Not expert)

for example : where to type my printer name

I am using Windows 11 64bit system with Office 2021 license user

 

[NdNoviceHlp]

As indicated, place in module code. To operate use the code as provided by Jaafar....

Public Sub PrintAddress()
    PrintToSpecificPrinter ObjectToPrint:=Sheets("Sheet1").Range("A11:E19"), PrinterName:="Brother QL-800"
End Sub

You can also place this in module code. Adjust the sheet, range and printer name as needed and run the PrintToSpecificPrinter sub. HTH. Dave

 

[Jagat Pavasia]

In a Standard Module:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function GetDefaultPrinter Lib "winspool.drv" Alias "GetDefaultPrinterA" (ByVal sPrinterName As String, lPrinterNameBufferSize As Long) As Long
    Private Declare PtrSafe Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long
#Else
    Private Declare Function GetDefaultPrinter Lib "winspool.drv" Alias "GetDefaultPrinterA" (ByVal sPrinterName As String, lPrinterNameBufferSize As Long) As Long
    Private Declare Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long
#End If


Sub PrintToSpecificPrinter( _
    ByVal ObjectToPrint As Object, _
    ByVal PrinterName As String _
)
    Dim sCurrentPrinter As String
 
    sCurrentPrinter = GetActivePrinter
    If SetDefaultPrinter(PrinterName) Then
        ObjectToPrint.PrintOut
        Call SetDefaultPrinter(sCurrentPrinter)
    Else
        MsgBox "Printing failed.", vbCritical
    End If

End Sub

Private Function GetActivePrinter() As String
    Dim sBuffer As String * 128, lBuffSize As Long
    lBuffSize = 128
    If GetDefaultPrinter(sBuffer, lBuffSize) Then
        GetActivePrinter = Left(sBuffer, lBuffSize - 1)
    End If
End Function

Usage of the above code :

Sub Test()
    PrintToSpecificPrinter ObjectToPrint:=Range("A11:E19"), PrinterName:="Brother QL-800"
End Sub

Dear sir both VBA code paste in to one sheet ?
I can not understand very well,
please tell me in detail.

I am a new in VBA code

 

[NdNoviceHlp]

Go to "Insert" in the Visual Basic Editor (as shown above), select "Module" and place the code in there. Also, that is not the code I provided? Dave