Print Button to print with different Printer types for different sheet

[bootdat]

I have 3 different sheets with a print button assigned to a macro.

two of these sheets, i want the macro to use Microsoft Print to PDF to print out the printing area
and i want the other sheet to use an EPSON Thermal printer

My problem is, when i select the Microsoft print to PDF as the printer, all three sheets prints with that
and when i also select the EPSON Thermal printer as the printer, all three sheets prints with that

I want my micros to know which Printer to use depending on the sheet I'm printing from

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTpePDF, _
    Filename:="C:\XX\mod\" & Range("E9").Value & "_" & Range("H6").Value & ".pdf", _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

Thats the vba code i use for the printing

Thank you

 

[severynm]

To do that, all you need to do is change the active printer before you print out. To set the active printer you need both the printer name and the port name as a string (reference). The easiest way to do that is to set the active printer via the normal print menu, then type Debug.Print Application.ActivePrinter in the immediate window (ctrl+g from the code editor). It should return something like PrinterName on port:. For example, ET0021B7E2F9D1 on Ne04: and CutePDF Writer on CPW3: are two of mine. From there, all you need to do to print to the desired printer is set (for example)

Application.ActivePrinter = "ET0021B7E2F9D1 on Ne04:"

I'm not sure exactly, but the way it sounds like you have your macro set up is you have a button on each of those three sheets which all call the same piece of code. Getting the source of where that function was called is a little more difficult. From my experience, you have two options:

1. You can make three different functions (one for each button) that only differ in the printer they set as Active.
2. If you are using ActiveX buttons and not form controls (the difference), you can use the _Click method of the buttons to call your print code in a different module and pass a parameter of the current worksheet. I.e.

Private Sub Button1onSheet1_Click()    
    Module1.PrintToCorrectPrinter Me.Name
End Sub

And your printing code is then

Public Sub PrintToCorrectPrinter(sheetName As String)
    If sheetName = "xxxx" Then
        Application.ActivePrinter = "ET0021B7E2F9D1 on Ne04:"
    ElseIf sheetName = "xxxx" Then
         Application.ActivePrinter = "CutePDF Writer on CPW3:"
    Elseif ...
         ....
    End If

    Sheet1.PrintOut
End Sub

 

[bootdat]

Hi.. thanks for your response

I'm try that out and give a feedback.

Thanks

To do that, all you need to do is change the active printer before you print out. To set the active printer you need both the printer name and the port name as a string (reference). The easiest way to do that is to set the active printer via the normal print menu, then type Debug.Print Application.ActivePrinter in the immediate window (ctrl+g from the code editor). It should return something like PrinterName on port:. For example, ET0021B7E2F9D1 on Ne04: and CutePDF Writer on CPW3: are two of mine. From there, all you need to do to print to the desired printer is set (for example)

Application.ActivePrinter = "ET0021B7E2F9D1 on Ne04:"

I'm not sure exactly, but the way it sounds like you have your macro set up is you have a button on each of those three sheets which all call the same piece of code. Getting the source of where that function was called is a little more difficult. From my experience, you have two options:

1. You can make three different functions (one for each button) that only differ in the printer they set as Active.
2. If you are using ActiveX buttons and not form controls (the difference), you can use the _Click method of the buttons to call your print code in a different module and pass a parameter of the current worksheet. I.e.

Private Sub Button1onSheet1_Click()   
    Module1.PrintToCorrectPrinter Me.Name
End Sub

And your printing code is then

Public Sub PrintToCorrectPrinter(sheetName As String)
    If sheetName = "xxxx" Then
        Application.ActivePrinter = "ET0021B7E2F9D1 on Ne04:"
    ElseIf sheetName = "xxxx" Then
         Application.ActivePrinter = "CutePDF Writer on CPW3:"
    Elseif ...
         ....
    End If

    Sheet1.PrintOut
End
[/QUOTE]

 

[bootdat]

To do that, all you need to do is change the active printer before you print out. To set the active printer you need both the printer name and the port name as a string (reference). The easiest way to do that is to set the active printer via the normal print menu, then type Debug.Print Application.ActivePrinter in the immediate window (ctrl+g from the code editor). It should return something like PrinterName on port:. For example, ET0021B7E2F9D1 on Ne04: and CutePDF Writer on CPW3: are two of mine. From there, all you need to do to print to the desired printer is set (for example)

Application.ActivePrinter = "ET0021B7E2F9D1 on Ne04:"

I'm not sure exactly, but the way it sounds like you have your macro set up is you have a button on each of those three sheets which all call the same piece of code. Getting the source of where that function was called is a little more difficult. From my experience, you have two options:

1. You can make three different functions (one for each button) that only differ in the printer they set as Active.
2. If you are using ActiveX buttons and not form controls (the difference), you can use the _Click method of the buttons to call your print code in a different module and pass a parameter of the current worksheet. I.e.

Private Sub Button1onSheet1_Click()   
    Module1.PrintToCorrectPrinter Me.Name
End Sub

And your printing code is then

Public Sub PrintToCorrectPrinter(sheetName As String)
    If sheetName = "xxxx" Then
        Application.ActivePrinter = "ET0021B7E2F9D1 on Ne04:"
    ElseIf sheetName = "xxxx" Then
         Application.ActivePrinter = "CutePDF Writer on CPW3:"
    Elseif ...
         ....
    End If

    Sheet1.PrintOut
End Sub

Hi, sorry for the late response...

so it happened that all i had to do was to set the desired printer.

so i added

Application.ActivePrinter = "EPSON TM-T20II Receipt5 on Ne05:" 'use here your printer

to the code where the document is needed to be printed on that printer

finalizing it as:

Application.ActivePrinter = "EPSON TM-T20II Receipt5 on Ne05:" 'use here your printer
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTpePDF, _
    Filename:="C:\XX\mod\" & Range("E9").Value & "_" & Range("H6").Value & ".pdf", _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

and did same to the other code where i need it to print as Microsoft Print to PDF

Application.ActivePrinter = "Microsoft Print to PDF on Ne01:" 'use here your printer
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTpePDF, _
    Filename:="C:\XX\mod\" & Range("E9").Value & "_" & Range("H6").Value & ".pdf", _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

so that worked for me

thanks very much..