Setting the active printer

[Guest]

In my office system, I am running XPPro SP2. I have one printer attached
locally to the computer but I have set it up as two printers, each with a
different "profile". One "profile" prints on a single side and the other
prints double sided.

I'm having a problem printing to a selected printer. If I manually set one
of the printers as the default in "Printers and Faxes" and set the
ActivePrinter to the other printer in VBA, Excel prints to the one I
initially specified as default. The ActivePrinter variable is set without
any errors so the printer name must be correct. I obtained the printer names
by setting the default printer manually and typing ?ActivePrinter in the
immediate window. It just doesn't printer to the ActivePrinter.

What I would like to do is have the user click a command button which sets
the ActivePrinter to a specific printer. Then, all subsequent print jobs
print to that printer until a different command button is clicked setting the
ActivePrinter to a different printer.

In other words, Excel prints to whichever printer is specified as the
default regardless if I set the ActivePrinter in VBA. I would like to avoid
using Application.Dialogs(xlDialogPrint).Show.

Any suggestions would be greatly appreciated.

Thank you for your time and expertise.

 

[Simon Lloyd]

Try this, you can enter the printer name where you see Microsoft fax on
fax, you could use an input box to enter the printer name or something
like that!
Regards,

Simon

Sub PrintToAnotherPrinter()
Dim STDprinter As String
STDprinter = Application.ActivePrinter
Application.ActivePrinter = "microsoft fax on fax:"
' change printer
ActiveSheet.PrintOut
' prints the active sheet
Application.ActivePrinter = STDprinter
' change back to standard printer
End Sub

 

[Simon Lloyd]

With Input Box!

Sub PrintToAnotherPrinter()
Dim STDprinter As String
Dim I2 As Integer
For I2 = 1 To 1
t1 = InputBox("Enter Printer Address", "Where to Print?", "")
STDprinter = Application.ActivePrinter
Application.ActivePrinter = t1.Value
ActiveSheet.PrintOut
Application.ActivePrinter = STDprinter
Next
End Su

 

[Guest]

Simon,

Thanks for the response. The problem is not how ActivePrinter is set.

Here is the problem in simplified form.

If I manually go to "Printers and Faxes" and set 'Printer A' as the default
printer and then click a command button with the code:

Private Sub CommandButton1_Click()
Application.ActivePrinter = "Printer B"
ActiveSheet.Printout
End Sub

The computer will print to Printer A, not B. For me to print to Printer B,
I must go back to "Printers and Faxes" and set 'Printer B' as the default.

In other words, setting ActivePrinter doesn't seem to do anything.

Howard