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]

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

 

[Jagat Pavasia]

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


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

I have enter this VBA code in my sheet, and it is working as it should,
but when I single click on macro then it did not print out from brother QL-800
when I click second time on macro then print.

i want print in single click

 

[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 meView attachment 80472

I WANT PRINT IN ONLY MY BROTHER QL-800 FROM THIS SHEET

 

[Jagat Pavasia]

I HAVE 3 PRINTER DRIVER INSTALLED IN MY COMPUTER.

ON OF THEM IS BROTHER LABEL PRINTER.

I WANT TO PRINT ON MY BROTHER PRINTER NAMED "Brother QL-800" IN SELECTED RANGE IS "A11:E19"

SO, I WANT MACRO THAT IF I SELECT MACRO & THEN AUTOMATICALLY SELECT MY BROTHER PRINTER AND INSTANT PRINT FROM "A11:E19".
(Printer should be automatically select "Brother QL-800" & then print)


PLEASE GIVE ME VBA CODE, I AM NEW IN VBA.

 

[Jaafar Tribak]

Hi Jagat,

I have already given you the code. I don't know why the code doesn't work for you upon the first click on the PrintAddress macro.

Try debugging the code by stepping through pressing the F8 key and see if you can figrue out where the problem lies.

 

[Jagat Pavasia]

Hi Jagat,

I have already given you the code. I don't know why the code doesn't work for you upon the first click on the PrintAddress macro.

Try debugging the code by stepping through pressing the F8 key and see if you can figrue out where the problem lies.

dear jaafar Tribak,

It is working but not as I want.

for example : My current default printer is selected to "Samsung SCX-3200". (In Printer Management in setting of Printer Properties)
and now I print from clicking PrintAddress macro then did not print on my "Brother QL-800",
The print come out from Samsung Printer, I want Print from Brother Ql-800.

I want only that if I click macro on PrintAddress then only print from Brother QL-800, even my default printer selection is whatever.

Please help me

waiting for your answer.

and sorry for my argument..

 

[Jaafar Tribak]

Hi Jagat, I understand what you want and I also understand the problem you are having.

The ode I gave you works for me just fine . I couldn't repoduce the issue you are describing. That's why I asked you to try to debug the code and see if you can figure out why it is not working for you.

What happens when you run the following :

Call SetDefaultPrinter("Brother QL-800")

Does that change the default printer for you ?

 

[Jagat Pavasia]

Hi Jagat, I understand what you want and I also understand the problem you are having.

The ode I gave you works for me just fine . I couldn't repoduce the issue you are describing. That's why I asked you to try to debug the code and see if you can figure out why it is not working for you.

What happens when you run the following :

Call SetDefaultPrinter("Brother QL-800")

Does that change the default printer for you ?

i found this error when i apply

 

[Jagat Pavasia]

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