Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Active Printer (2002/SP2)

    We have an Excel Spreadsheet that is used by many different people in the company. We just started printing to a new duplex printer. What I want to be able to do is to include a macro in the workbook that will print to the duplex printer and then reset to the users default printer. I have the macro set up and it works well but when I try running it on another computer it doesn't work. This is the line created when I ran the Record Macro

    Application.ActivePrinter = "laafp1LAA-Canon Duplex on Ne03:"

    The problem appears to be the Ne03, when I try to run the macro on a different computer, that's the line where it hangs. If I record a macro on a different computer, it comes up with a different Ne#.
    I did a search and found a few posts on this subject but they seem pretty complicated. Is there an easy way to make this work? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Active Printer (2002/SP2)

    The code in this newsgroup post can be adapted for your purpose:

    Copy the following function into a module:

    Function SetPrinter(strPrinter As String) As Boolean
    Dim i As Integer
    On Error Resume Next
    For i = 1 To 99
    Err.Clear
    Application.ActivePrinter = strPrinter & " on Ne" & Format(i, "00") & ":"
    If Err.Number = 0 Then
    SetPrinter = True
    Exit For
    End If
    Next i
    End Function

    Use like this:

    <img src=/w3timages/blueline.gif width=33% height=2>

    If SetPrinter("laafp1LAA-Canon Duplex") = False then
    MsgBox "Can't find printer!", vbExclamation
    Exit Sub
    End If

    ' Code to print goes here
    ...

    <img src=/w3timages/blueline.gif width=33% height=2>

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Active Printer (2002/SP2)

    Hans,
    Thanks for the quick response. It worked but I had to REM out one line, Application.ActivePrinter = "laafp1LAA-Canon Duplex:", my code may be wrong. When I do REM that line, it doesn't reset back to the default printer. This is what I have, that runs after the code that you supplied.

    Dim strPrinter As String
    strPrinter = Application.ActivePrinter
    Application.ActivePrinter = "laafp1LAA-Canon Duplex:"
    ActiveWorkbook.PrintOut Copies:=1, ActivePrinter:="laafp1LAA-Canon Duplex:", Collate:=True
    Application.ActivePrinter = strPrinter

    I hope that made sense. Thanks for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Active Printer (2002/SP2)

    If

    ActiveWorkbook.PrintOut Copies:=1, ActivePrinter:="laafp1LAA-Canon Duplex:", Collate:=True

    works, I don't understand the original question. I thought the problem was that you had to specify the " on Ne03" part, but that can vary from PC to PC.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Active Printer (2002/SP2)

    I think the " on Ne03" was the problem and it is working now. I've tried it on 2 different computers. The only part that doesn't happen, is that it doesn't set the printer back to the printer that was selected before the macro runs.
    (what we are doing is printing this document to a Copier/Printer that duplexes, it's the only one we have, and then I want to set the users printer back to what they were using before. When they go to print their next document, if they forget to reset the printer, they'll wonder what happened to it. That step isn't critical but it would be a nice feature.)
    Thanks again.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Active Printer (2002/SP2)

    You seem to be setting strPrinter after running the code I supplied. If that is correct, the ActivePrinter has already been changed.

    Try this:

    Dim strPrinter As String
    strPrinter = Application.ActivePrinter
    SetPrinter "laafp1LAA-Canon Duplex"
    ActiveWorkbook.PrintOut Copies:=1, Collate:=True
    Application.ActivePrinter = strPrinter

Posting Permissions

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