# help with printer VBA code



## Jagat Pavasia (Dec 8, 2022)

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 (Dec 9, 2022)

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 (Dec 9, 2022)

```
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 (Dec 9, 2022)

Jagat Pavasia said:


> dear sir,
> 
> I have 3 printers named in my windows 10 PC:
> 1) Samsung SCX-3200
> ...


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


----------



## Jagat Pavasia (Dec 9, 2022)

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 (Dec 9, 2022)

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 (Dec 9, 2022)

Jaafar Tribak said:


> 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 (Dec 9, 2022)

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 (Dec 10, 2022)

Jaafar Tribak said:


> 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.
> 
> ...









i found this error when i apply


----------



## Jagat Pavasia (Dec 10, 2022)

*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


----------



## Jagat Pavasia (Dec 8, 2022)

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 (Dec 10, 2022)

Jagat Pavasia said:


> *I want something like*
> 
> Sub PrintAddress()
> Set mynetwork = CreateObject("WScript.network")
> ...


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

I tested your code and It worked ok for me.


----------



## Jagat Pavasia (Dec 10, 2022)

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 (Dec 15, 2022)

Jaafar Tribak said:


> 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 (Dec 15, 2022)

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 (Dec 16, 2022)

NdNoviceHlp said:


> 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....
> 
> ```
> ...


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 (Dec 16, 2022)

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 (Dec 16, 2022)

Jaafar Tribak said:


> In a Standard Module:
> 
> ```
> Option Explicit
> ...


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 (Dec 16, 2022)

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


----------

