VBA print to parameterised printer

[Sjun]

Hi there,

I just registered on this forum, hoping someone can help me at the following issue;

I would like to have the ability to set a printername/number in cell A1, and use this cell as the name for the printer to where my printjobs get sent.

This way, I can set the printername, and use a button which activates a macro to print to a specific printer.

So far, I have this code, executed on buttonpress:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "\\printserver\printer1 on Ne13:", Collate:=True

What I would like to have (but working) is:

ActivePrinter:=  "\\printserver\" & Worksheets("Sheet1").A1 
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

 

[Andrew Poulsom]

Welcome to the Board.

Like this?

Application.ActivePrinter = "\\printserver\" & Worksheets("Sheet1").Range("A1").Value

 

[Sjun]

Welcome to the Board.

Like this?

Application.ActivePrinter = "\\printserver\" & Worksheets("Sheet1").Range("A1").Value

Hello Andrew, thank you for your help.

Although that looks like exactly what I want, it doesn't work.
I have also tried

Application.ActivePrinter = "\\printserver\" & ActiveSheet.Range("A1").Value

If I run the macro it says (translated from dutch)
error 1004 at runtime:
Method Activeprinter of object _Application failed.

I also tried to concatenate the string and cellvalue in a variable "MyPrinter" first, and then call

Application.ActivePrinter = MyPrinter

But that also fails.

 

[Sjun]

Finally, after trying lots of code, and with some help from this post, I managed to get it working:

Dim MyPrinter As String
MyPrinter = ("\\printserver\" & Range("A1").Value)
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=MyPrinter, Collate:=True

Thanks for pointing me in the right direction