VBA. How to loop on error, e.g. 01,02,03,04.....10,11,etc

**rain4u** · 04-05-2012, 03:26 AM

Hi
I have a code as below that first records the details of current active printer, then changes the active printer to a different network printer, and then switches back to a original printer.

Sub final_printer_VBA()
Dim STDprinter As String

'---VVV---- record the details of default printer
STDprinter = Application.ActivePrinter

'---VVV---- change printer to another network printer. _
'Range("printerDetails").Value refers to a cell that in my scenario contains value "\\APP103VMFLT\FLTPLT012" (without quotes)

'----------------------------------------------------------------VVV----
Application.ActivePrinter = Range("printerDetails").Value & " on Ne03:" '<<< this is where I need help
'----------------------------------------------------------------AAA---_
'How to set up a loop so it would start with Ne01 and on error would loop through to Ne16 if required, until it finds the correct one?


'---VVV---- print dialog box. I will change this to print x 1 copy
Application.Dialogs(xlDialogPrint).Show

'---VVV---- change active printer back to default printer
Application.ActivePrinter = STDprinter

End Sub

I would need a bit of help with

Application.ActivePrinter = Range("printerDetails").Value & " on Ne03:"

namely how can I create a loop with Ne03 so it would loop from Ne01 to Ne16.
E.g.
if it errors with Ne01, then it tries Ne02, if it fails with Ne02 it then tries Ne03, etc, etc.
So lets say the right answer is Ne15, and in this scenario it would have to loop through with Ne01 to Ne14 and on only proceed with the code with Ne15.

Any ideas? As always I would appreciate any help. Cheers.

**Andrew-R** · 04-05-2012, 04:40 AM

I don't think I've ever used the Application.ActivePrinter method, but I'm assuming it throws an error if you try to assign a printer which doesn't exist.

If so then something like this might work:

Sub final_printer_VBA()
Dim STDprinter As String
Dim lPrinterNum As Long

'---VVV---- record the details of default printer
STDprinter = Application.ActivePrinter

'---VVV---- change printer to another network printer. _
'Range("printerDetails").Value refers to a cell that in my scenario contains value "\\APP103VMFLT\FLTPLT012" (without quotes)

lPrinterNum=1

On Error GoTo PrinterError

ResumePoint:

'----------------------------------------------------------------VVV----
Application.ActivePrinter = Range("printerDetails").Value & " on Ne" & Format(lPrinterNum,"00") & ":" '<<< this is where I need help
'----------------------------------------------------------------AAA---_
'How to set up a loop so it would start with Ne01 and on error would loop through to Ne16 if required, until it finds the correct one?

On Error GoTo 0 'Turns off error trapping

'---VVV---- print dialog box. I will change this to print x 1 copy
Application.Dialogs(xlDialogPrint).Show

'---VVV---- change active printer back to default printer
Application.ActivePrinter = STDprinter

Exit Sub

PrinterError:

lPrinterNum=lPrinterNum+1

If lPrinterNum>30 Then 'You need this test in to stop your code looping forever if no printer is found, but you can set this value as high as you like
  MsgBox "No Printers were found"
Else
  Resume ResumePoint
End If

End Sub

Edited to add: If Excel doesn't throw an error until you try to open the printer dialog then move the "On Error GoTo 0" line to after the call to the printer dialog.