Selecting a Printer in VBA

[Robin Clay]

Greetings !

and a HAPPY NEW YEAR !



We have two printers, a B&W HP laserjet (A4)
and a colour HP Bubblejet (A3 or A4).

In one of my Excel workbooks,
several charts are generated, and a table.

I have a VBA routine that allows the user to print out
any required combination by entering a number,
e.g. enter 7 to print out Charts 3 & 4 and the table,
and the routine then allows the user
to select the approriate printer for their selection.

Here's a segment of what I have :-

' Range("D5") = "\\HCE_TREE\lond_hp_lj4000n_q on Ne01:"
' Range("D6") = "DESKJET 1220C on Ne00:"
'On Error GoTo Fin

Printer(1) = Worksheets("Long Plot").Range("D5")
Printer(2) = Worksheets("Long Plot").Range("D6")

N = InputBox("Enter 1 for B & W" & vbCrLf & _
"Enter 2 for Colour", "Select Printer")
If N < 1 Or N > 2 Then GoTo Fin

Application.ActivePrinter = Printer(N)


....and this used to work just FINE.

However, "they" have issued me with a new computer, and
they have also been fiddling about with the Server, and as
a result this segement no longer works, generating this
error on the last line:

-------[ Error ]----

Run time error '1004'
Method 'ActivePrinter' of object '_Application' failed

--------------------


If I click on Control Panel > Printers
and then click on "Properties" for these two printers,
then under "Ports" I get :

Port: IP_192.168.1.201 Printer: HP DeskJet 1220C
Port: IP_192.168.1.206 Printer: HP LJ 4000N

I have tried putting into the relevant Cell
in the worksheet the values -

IP_192.168.1.201
HP DeskJet 1220C

both with and without the new pre-fix \\LOND\
and also omitting the IP_
etc., etc., etc..... but nothing will work !


Our IT Dept cannot help,
as "this is a specialist Excel / VBA problem"


H E L P !


RClay AT haswell DOT com

 

[Bob Phillips]

Robin,

Why not just use the printer dialog and let the user choose?

Application.Dialogs(xlDialogPrint).Show


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Robin Clay]

Why not just use the printer dialog

and let the user choose?
Application.Dialogs(xlDialogPrint).Show

Well, that works a Treat - Thank you !

However... since you ask...
The idea was to enter 1 for B&W or 2 for colour.
Then, if 2 were chosen,
a further choice is offered, A3 or A4.


Regards

Robin


RClay AT haswell DOT com

 

[Paul Davison]

Robin

I have found that the network reference for the networked
printers can tend to be variable and sometimes change for
no reason. Note that one of your printers is Ne01 and
the other is Ne00. When you have a problem like you are
having I recommend that you record a macro of the
following actions: File, Print, select the printer, press
Close and stop recording. Then look in the resulting VBA
module to see what the printer name is and adjust you
existing macro to suit.

Paul Davison

-----Original Message-----
Robin,

Why not just use the printer dialog and let the user choose?

Application.Dialogs(xlDialogPrint).Show


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Greetings !

and a HAPPY NEW YEAR !



We have two printers, a B&W HP laserjet (A4)
and a colour HP Bubblejet (A3 or A4).

In one of my Excel workbooks,
several charts are generated, and a table.

I have a VBA routine that allows the user to print out
any required combination by entering a number,
e.g. enter 7 to print out Charts 3 & 4 and the table,
and the routine then allows the user
to select the approriate printer for their selection.

Here's a segment of what I have :-

' Range("D5") = "\\HCE_TREE\lond_hp_lj4000n_q on Ne01:"
' Range("D6") = "DESKJET 1220C on Ne00:"
'On Error GoTo Fin

Printer(1) = Worksheets("Long Plot").Range("D5")
Printer(2) = Worksheets("Long Plot").Range("D6")

N = InputBox("Enter 1 for B & W" & vbCrLf & _
"Enter 2 for Colour", "Select Printer")
If N < 1 Or N > 2 Then GoTo Fin

Application.ActivePrinter = Printer(N)


...and this used to work just FINE.

However, "they" have issued me with a new computer, and
they have also been fiddling about with the Server, and as
a result this segement no longer works, generating this
error on the last line:

-------[ Error ]----

Run time error '1004'
Method 'ActivePrinter' of object '_Application' failed

--------------------


If I click on Control Panel > Printers
and then click on "Properties" for these two printers,
then under "Ports" I get :

Port: IP_192.168.1.201 Printer: HP DeskJet 1220C
Port: IP_192.168.1.206 Printer: HP LJ 4000N

I have tried putting into the relevant Cell
in the worksheet the values -

IP_192.168.1.201
HP DeskJet 1220C

both with and without the new pre-fix \\LOND\
and also omitting the IP_
etc., etc., etc..... but nothing will work !


Our IT Dept cannot help,
as "this is a specialist Excel / VBA problem"


H E L P !


RClay AT haswell DOT com

.