Selecting a Printer in VBA

Discussion in 'Microsoft Excel Programming' started by Robin Clay, Jan 15, 2004.

  1. Robin Clay

    Robin Clay Guest

    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
     
    Robin Clay, Jan 15, 2004
    #1
    1. Advertisements

  2. Robin Clay

    Bob Phillips Guest

    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" <> wrote in message
    news:07e601c3db53$ebaedd20$...
    > 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, Jan 15, 2004
    #2
    1. Advertisements

  3. Robin Clay

    Robin Clay Guest

    > 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
     
    Robin Clay, Jan 15, 2004
    #3
  4. Robin Clay

    Paul Davison Guest

    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" <> wrote

    in message
    >news:07e601c3db53$ebaedd20$...
    >> 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

    >
    >
    >.
    >
     
    Paul Davison, Jan 28, 2004
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Robin Clay

    Selecting a printer

    Robin Clay, Feb 2, 2004, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    193
    Robin Clay
    Feb 2, 2004
  2. BrianG

    Selecting printer from macro

    BrianG, Jun 9, 2004, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    268
    keepITcool
    Jun 9, 2004
  3. Minitman

    Selecting A Printer With VBA

    Minitman, Oct 21, 2004, in forum: Microsoft Excel Programming
    Replies:
    4
    Views:
    831
    Tom Ogilvy
    Oct 21, 2004
  4. mikeburg

    Send printer escape commands to a printer using VBA

    mikeburg, Oct 6, 2005, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    1,069
    Dave Peterson
    Oct 6, 2005
  5. Dennis Pedersen

    store default printer, change printer and then restore default printer?

    Dennis Pedersen, Nov 14, 2007, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    1,202
    Dennis Pedersen
    Nov 14, 2007
Loading...

Share This Page