Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to change to a specific printer (Excel 2002 SP2)

    I have a user who created a recorded Excel macro that changes their active printer in Excel to one located on another floor. The macro then prints and sets them back to their current printer. It works fine until we add new printers or re-build their PCs and install printers in a different order (because of the NeXX: number). It is also used by several people, complicating the printer installation process. I am trying to fix this problem for them but I am more experienced in Word VBA. I have used this type of code in Word and it works fine:

    Sub Print_sheet_elsewhere()
    Dim strDefPrinter As String
    Dim strSwiftPrinter As String
    strDefPrinter = Application.ActivePrinter
    strSwiftPrinter = "RBNZ_DCKSG-DC1"
    Application.ActivePrinter = strSwiftPrinter
    ActiveWindow.PrintOut Copies:=1
    Application.ActivePrinter = strDefPrinter
    End Sub

    When I try the same code in Excel, it refuses to set the Activeprinter until you specify it this way:

    Sub Print_sheet_elsewhere()
    Dim strDefPrinter As String
    Dim strSwiftPrinter As String
    strDefPrinter = Application.ActivePrinter
    strSwiftPrinter = " RBNZ_DCKSG-DC1 on NE03:"
    Application.ActivePrinter = strSwiftPrinter
    ActiveSheet.PrintOut Copies:=1
    Application.ActivePrinter = strDefPrinter
    End Sub

    I searched through posts here and found one (number 55355) that suggested doing something like..........

    CurPrntr= Application.ActivePrinter
    If Right(CurPrntr,5)="Ne00:" then Application.ActivePrinter = _
    PrinterOnNE01
    Else Application.ActivePrinter = PrinterOnNE00
    Print Routine here
    Application.ActivePrinter = CurPrntr

    But some of our people have up to 10 network printers installed, meaning I would have to loop through them... Can anyone suggest a way I can do this? Or alternatively can you specify the printer without finding the NE number as you can in word.

    Belinda

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Macro to change to a specific printer (Excel 2002 SP2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Belinda

    I suggest you display the Choose printer dialog and have the user pick the printer.

    If you need more help, let me know and I'll try and post a bit of code for you.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to change to a specific printer (Excel 2002 SP2)

    Hi Belinda,

    You can use this code to enumerate all installed printers. Set a reference to the Windows scripting host library (tools, references in the VBE) and copy this code:

    <pre>Option Explicit

    Sub PrintElsewhere()
    Dim wshNetwork As Object
    Dim oDrives As Object
    Dim oPrinters As Object
    Dim iCount As Integer
    Dim sCurrentprinter As String
    sCurrentprinter = Application.ActivePrinter
    Set wshNetwork = CreateObject("WScript.Network")
    Set oDrives = wshNetwork.EnumNetworkDrives
    Set oPrinters = wshNetwork.EnumPrinterConnections
    For iCount = 0 To oPrinters.Count - 1 Step 2
    'Change to whatever printer you need
    If InStr(oPrinters.Item(iCount + 1), "Canon iR2200") > 0 Then
    Application.ActivePrinter = oPrinters.Item(iCount + 1) & " on " & oPrinters.Item(iCount)
    'Now print, or whatever
    Exit For
    End If
    'put it back to user's setting
    Application.ActivePrinter = sCurrentprinter
    Next
    End Sub


    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Lounger
    Join Date
    Apr 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to change to a specific printer (Excel 2002 SP2)

    Thanks that looks like it will do what I want, I will give it a try today

    Belinda

  5. #5
    Lounger
    Join Date
    Apr 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to change to a specific printer (Excel 2002 SP2)

    Jan,

    I gave it a go and got the same error (Method active printer of object Application failed)... So I set the line below in a new string to see what it had in it.....

    sNewPrinter = oPrinters.Item(iCount + 1) & " on " & oPrinters.Item(iCount)

    and it was set to "RBNZ_DCKSG-DC1 on 172.27.31.40:LP".... so I used

    Application.ActivePrinter = sNewPrinter

    but I got the same error. So it's finding the right printer but usng the IP address not the NE number????

    Can you help?

    Belnda

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to change to a specific printer (Excel 2002 SP2)

    Hmmm. It worked OK for me. And I must admit I found the code elsewhere, so I cannot help you much with changing this so it will work.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to change to a specific printer (Excel 2002 SP2)

    Hi,

    Fiddled around a bit.

    This is not pretty, but it seems to work OK:

    Option Explicit

    Sub GetPrinterList()
    Dim sPrinters() As String
    Dim lPrinterCount As Long
    Dim lCount As Long
    Dim sCurPrinter As String
    Dim sTemp As String
    Dim sMsg As String
    ReDim sPrinters(1)
    lPrinterCount = 0
    sCurPrinter = Application.ActivePrinter
    Do
    sPrinters(lPrinterCount) = sTemp
    lPrinterCount = lPrinterCount + 1
    SendKeys "^p^{home}{down " & lPrinterCount - 1 & "}~"
    Application.Dialogs(xlDialogPrinterSetup).Show
    sTemp = Application.ActivePrinter
    ReDim Preserve sPrinters(lPrinterCount)
    Loop Until sTemp = sPrinters(lPrinterCount - 1)
    For lCount = 1 To lPrinterCount
    sMsg = sMsg & sPrinters(lCount) & vbNewLine
    Next
    MsgBox sMsg
    Application.ActivePrinter = sCurPrinter
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Lounger
    Join Date
    Apr 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to change to a specific printer (Excel 2002 SP2)

    Thanks for that second piece of code, you are so quick!. That worked for me but the screen display as it built the list of printers wouldn't have been acceptable to the users.

    I was researching the EnumPrinterConnections to try and figure out why it wouldn't work and found confirmation that it outputs IP addessess for network printers. As I couldn't persuade Excel to accept this I was still looking for another solution. What I found was another Windows Scripting Host command that sets the default printer and it doesn't care about NE numbers!!

    The code I am currently testing with is below and seems to be doing the job....

    Sub PrintElsewhere()
    Dim wshNetwork As Object
    Dim iCount As Integer
    Dim sCurrentprinter As String
    Dim sCurrentPos As String
    Dim sDefaultprinter As String
    Dim sNewPrinter As String
    sCurrentprinter = Application.ActivePrinter
    Set wshNetwork = CreateObject("WScript.Network")
    On Error GoTo NoSwift
    wshNetwork.SetDefaultPrinter "RBNZ_DCSWIFT"
    ActiveSheet.PrintOut Copies:=1
    GoTo ResetCurrentPrinter
    NoSwift:
    MsgBox ("You need to install the Swift 6th Floor Printer then run the macro again")
    Goto Finish
    ResetCurrentPrinter:
    'Had to get rid of ......on NeXX as the wsh command doesn't like it!
    sCurrentPos = InStr(1, sCurrentprinter, "on", vbTextCompare)
    sDefaultprinter = Left(sCurrentprinter, sCurrentPos - 2)
    wshNetwork.SetDefaultPrinter sDefaultprinter
    Finish:
    End Sub

    Hope this helps someone else and thanks for all your help!!!

    Belinda

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to change to a specific printer (Excel 2002 SP2)

    Ah, that is better indeed.

    Thanks for letting us know.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Macro to change to a specific printer (Excel 2002 SP2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Jan Karel Pieterse

    I want to ask you why is this needed in the code you have?

    Set oDrives = wshNetwork.EnumNetworkDrives

    What does it do?

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to change to a specific printer (Excel 2002 SP2)

    Uhhhmmmm <img src=/S/hiding.gif border=0 alt=hiding width=70 height=24> I have no idea. Must have forgotten to remove that line from the code sample. It was part of a larger project in which other things were done as well.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •