Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    44
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Print Macro for Excel 2007

    I'm trying to pull together VBA code in Excel that does the following:
    capture current [default] printer info
    change printer to another on the network
    print to a specific tray
    change printer back to default

    I found code that produces a list of available printers, but I don't know how to change to the one I want.

    Can anyone help me?
    tia ~

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about something like (change the desired printer as appropriate)


    Option Explicit
    Sub ChangePrinter()
    Dim sCurPrinter As String
    sCurPrinter = Application.ActivePrinter
    Application.ActivePrinter = "\\morrsp\MOR9QT on Ne04:"
    ActiveSheet.PrintOut preview:=True
    Application.ActivePrinter = sCurPrinter
    End Sub

    Steve

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    44
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Steve -
    Thank you for that! My problem is that the printer I want to change to is on a different port for different users. One user has "on Ne04:" but another has "on NeO9:"

    The snippet of code I found on the Internet has this:
    Sub Test2()
    Dim sConn As String
    Dim WshNetwork As Object
    Dim oPrinters As Object
    Dim i As Long
    Dim avTmp As Variant
    Dim MyPrinter As String

    avTmp = Split(Excel.ActivePrinter, " ")
    sConn = " " & avTmp(UBound(avTmp) - 1) & " "
    Set WshNetwork = CreateObject("WScript.Network")
    Set oPrinters = WshNetwork.EnumPrinterConnections
    MyPrinter = ActivePrinter
    For i = 0 To oPrinters.Count - 1 Step 2
    If InStr(1, oPrinters.Item(i + 1), "3", _
    vbTextCompare) > 0 Then
    ActivePrinter = oPrinters.Item(i + 1) & _
    sConn & oPrinters.Item(i)
    Exit For
    End If
    Next
    Activesheet.printout
    ActivePrinter = MyPrinter
    End Sub

    It looks like it should work like a champ - but it always bombs out on the text in red. Mehod ActivePrinter of object _Global failed. I can't find any solutions for this error anywhere!!

    M

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What are the values of oPrinters.Item(i + 1) & sConn & oPrinters.Item(i) when it causes the error? I presume it is not a valid printer name. I think some of the printers in printer connections get called USBs and not NEs which may be the problem...

    I have seen some code to loop through the NEs until it finds it but this may work:
    http://www.dailydoseofexcel.com/arch...-printer-port/
    Steve

Posting Permissions

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