1

I have a for loop where near the top I expect to throw an error trying to set a variable equal to a workbook. The error is returned because the workbook may or may not exist. The for loop loops over many workbooks and any number of them may or may not exist. the loop looks something like this

for i = 1 to x
     'get ready for to open the workbook

     set = myworkbook = workbooks.open("path\myworkbook" & date)

     'do a bunch of stuff after I get the workbook

next i

this works all fine and dandy, but if the workbook doesn't exist, then I get an error. Now I have tried a handful of error handling techniques. Ideally what happens is that if the workbook isn't found, it skips right down to the bottom of the loop and just goes into the next i.

I have used goto statements to just jump to the bottom of the line, but that only works once and on the second pass through when the workbook doesn't exist, that throws an error. I have tried err.Clear after the the goto line so that the new error can be caught. I have tried goto -1 to clear the error but that doesn't work either. I have tried a variety of resume next statements but if the error doesn't occur because the workbooks are found, then that throws an error which I can't seem to handle. I check the error number before it starts the error handling routine wrapping the variable setting line to make sure that it shows 0 but it still throws the error.

There seem to be a lot of methods to solve this problem and I think I've tried them all. This isn't a unique problem I'm certain and I just need a push in the right direction. Thanks for any help you guys might provide.

Just so you don't think I haven't tried whatever solution you think of first, here are some other questions I've read:

For Loop, how to skip iterations

Difference between 'on error goto 0' and 'on error goto -1' -- VBA

On Error Goto 0 not resetting error trapping

Access VBA: Is it possible to reset error handling

Continue For loop

Error handling only works once

Community
  • 1
  • 1
Dan White
  • 533
  • 1
  • 8
  • 18
  • Why not use `For each ws in myworkbook.Worksheets` see [SO Excel documentation topic](https://stackoverflow.com/documentation/excel-vba/1144/loop-through-all-sheets-in-active-workbook/3685/retrieve-all-worksheets-names-in-active-workbook#t=201707261350060188819) – Siyon DP Jul 26 '17 at 13:50
  • These are separate workbooks, not worksheets within a workbook. They all have a unique file name and path that is differentiated by the date. Notice the file name includes the date. – Dan White Jul 26 '17 at 15:29

2 Answers2

4

Why not something like this:

Public Sub SkipLoop()
   On Error Resume Next

   Dim i As Integer
   Dim wb As Workbook

   For i = 1 To 100
      Err.Clear
      Set wb = Workbooks.Open("some file")

      If Err.Number = 0 Then
         'do work on success
      End If
   Next
End Sub
Brian M Stafford
  • 8,483
  • 2
  • 16
  • 25
  • This worked for the first pass through, but not the second. If there were two successive errors, then the second error would be raised but not the first. I used message boxes to make sure that Err.Clear had removed error 1004 but the error was still thrown. – Dan White Jul 26 '17 at 15:26
  • @Dan I'm not seeing this behavior with the above code. – Brian M Stafford Jul 26 '17 at 15:46
2

No need to use error handling for something like this. Just use smart logic to your advantage:

Dim MyWorkbook as Workbook
for i = 1 to x
     'get ready for to open the workbook

     On Error Resume Next
     set myworkbook = workbooks.open("path\myworkbook" & date)
     On Error GoTo 0

    If Not MyWorkbook Is Nothing Then
        'do a bunch of stuff after I get the workbook
    End if
next i

What this does is checks for 'Nothingness' of the object variable. If the object isnt set (wasnt found) the code within the If block wont run.

Brandon Barney
  • 2,382
  • 1
  • 9
  • 18