PC Review


Reply
Thread Tools Rate Thread

Selecting a Printer in VBA

 
 
Robin Clay
Guest
Posts: n/a
 
      15th Jan 2004
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
 
Reply With Quote
 
 
 
 
Bob Phillips
Guest
Posts: n/a
 
      15th Jan 2004
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" <(E-Mail Removed)> wrote in message
news:07e601c3db53$ebaedd20$(E-Mail Removed)...
> 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



 
Reply With Quote
 
 
 
 
Robin Clay
Guest
Posts: n/a
 
      15th Jan 2004
> 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

 
Reply With Quote
 
Paul Davison
Guest
Posts: n/a
 
      28th Jan 2004
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)
>
>"Robin Clay" <(E-Mail Removed)> wrote

in message
>news:07e601c3db53$ebaedd20$(E-Mail Removed)...
>> 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

>
>
>.
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Not Selecting Field names When Selecting Some Data mcl Microsoft Access Queries 0 8th May 2008 12:57 AM
Re: Selecting whole word while selecting Charles Kenyon Microsoft Word Document Management 0 13th Apr 2005 08:14 PM
Excel dragging/selecting and not de-selecting. Mike D Microsoft Excel Crashes 5 21st Oct 2004 08:53 PM
Selecting A Printer With VBA Minitman Microsoft Excel Programming 4 21st Oct 2004 05:51 PM
Error message: The server for the "fill in printer model here" printer does not have the correct printer driver installed. -- Read on... - errorP.JPG (0/1) Tom Barnett Microsoft Windows 2000 Printing 0 14th Oct 2003 08:23 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:03 PM.