Need help with multiple error handling

[klarowe]

I have a report set up that has a simple button on the home page of the report that will print the report to PDF. All of the computers at my work use PrimoPDF on Ne00: and the code works great. However, our Houston shop computers use AdobePDF on TS002: So I need a way that it will check for one printer and if that errors out, it will go to the next printer, and then if that also errors out, I want it to go to a message box about a printing error. Here is the code I have right now that doesn't seem to work. It errors out to "1:" but then if I force an error (ie changing to PrimoPD on Ne00) then it gives a run-time error instead of my message box.
Thanks in advance.

Private Sub CommandButton1_Click()
    Select Case MsgBox("Are you sure you want to print to PDF?", vbYesNo, "Confirm printing")
        Case vbYes
            Call PrintSubCode
        Case vbNo
            End
    End Select
End Sub
 
Sub PrintSubCode()
      Dim strCurrentPrinter As String
        strCurrentPrinter = Application.ActivePrinter
        
        On Error GoTo 1
        Application.ActivePrinter = "AdobePDF on TS002:"
        ActiveWorkbook.PrintOut Copies:=1, Collate:=True
        Application.ActivePrinter = strCurrentPrinter
        Exit Sub
 
1:     On Error GoTo Error
        Application.ActivePrinter = "PrimoPDF on Ne00:"
        ActiveWorkbook.PrintOut Copies:=1, Collate:=True
        Application.ActivePrinter = strCurrentPrinter
        Exit Sub
 
Error:  MsgBox "An error has prevented this report from printing.", vbOKOnly, "Printing Error"
End Sub

 

[Andrew Poulsom]

You can't use On Error GoTo when an error handler is active. Try the untested:

Sub PrintSubCode()
    Dim strCurrentPrinter As String
    strCurrentPrinter = Application.ActivePrinter
    On Error Resume Next
    Application.ActivePrinter = "AdobePDF on TS002:"
    If Err <> 0 Then
        Err.Clear
        On Error GoTo 0
        On Error Resume Next
        Application.ActivePrinter = "PrimoPDF on Ne00:"
        If Err <> 0 Then
            Err.Clear
            On Error GoTo 0
            MsgBox "An error has prevented this report from printing.", vbOKOnly, "Printing Error"
            Exit Sub
        End If
    End If
    ActiveWorkbook.PrintOut Copies:=1, Collate:=True
    Application.ActivePrinter = strCurrentPrinter
End Sub

For further reading see:

http://www.cpearson.com/Excel/ErrorHandling.htm

 

[klarowe]

Ok. I will give that a shot as soon as I get the whole printer name figured out. Apparently our Houston shop uses a virtual desktop so the printer is actually "AdobePDF (from TBIDUSSKLEO) on TS002:" and I am having issues right now even getting it to call out that printer.

And actually... anyone have an idea on what I may have wrong with this issue?

 

[Andrew Poulsom]

And actually... anyone have an idea on what I may have wrong with this issue?

Didn't I answer that question?

 

[klarowe]

Didn't I answer that question?

Sorry, I meant with how I need to call out their printer. I sent the secretary down there a list of directions to record a macro that will hopefully give me the proper code to call their printer. Then I can try the code you posted up. I thought it would just be "AdobePDF on TS002:" but it wasn't.

 

[klarowe]

Now here is a shot out in left field... is there a way for it to set the activeprinter to find the first (or any) printer with "PDF" in the name and have it select that. The above code should fix the current issue, but I'm thinking in the future if anyone else tries to use it, I'll end up having to add each and every printing option and it could get to be a royal pain. If I could set it to just find "PDF" and use that printer then it should make everything a lot easier.

 

[Andrew Poulsom]

Here's some code that lists the available printers:

http://forums.devx.com/archive/index.php/t-79964.html

You could loop through the array looking for PDF and use that one.

 

[klarowe]

Here's some code that lists the available printers:

http://forums.devx.com/archive/index.php/t-79964.html

You could loop through the array looking for PDF and use that one.

Wow.. thats a little more involved than I was hoping. I may just list these 2 printers with the code you posted before and then instead of the message box on error I will just use Application.Dialogs(xlDialogPrint).Show and the person can select the printer. Much easier for the few instances that may come up.
Once I get this printer name issue figured out I will let you know if your code worked... but as of right now, it seems like it did.

 

[klarowe]

I got the printer name all figured out and the code is working perfectly.
Thanks!