Code to check if file ISN'T Open out of all open workbooks

[trillium]

Hi there
I have code to loop through all open workbooks to see if a file is open. I've been using this code for years and it works great. The target file changes it's name every month, so we use look for a constant word in the file name to locate it.

NOW, however, if the file ISN'T found within all the open books, I want the code to identify this too. (As I will make a pop-up message that the file isn't open and then code to go open the folder on our LAN... don't need help with this part). JUST can't seem to get it to loop through the open books to determine out of ALL the books, it isn't there.. keeps stopping at the first book that doesn't match and says, "Yup! Not found!" even if the file is open!

Here is my current code to FIND it:

' 1) loop through open books and look for the destination book (name contains "Dump")
  
  For iIdx = 1 To Workbooks.Count 'assumes won't have >30000 workbooks open so idx, interger variable takes up less space wb variable
    If InStr(1, UCase(Workbooks(iIdx).Name), "DUMP") > 0 Then
      Set wbSource = Workbooks(iIdx) 'referencing wb within index of workbooks
      Set wsSource = wbSource.Sheets(1)
      
      On Error Resume Next 'if an error is generated, don't pop up messages to the user, just resume next, keep on trucking
      On Error GoTo 0 'do regular error junk, set
      Exit For 'will take the first wb with "process", once found it works and exits
    End If
  Next iIdx

Thoughts for a simple solution?

 

[Blunder1]

you could keep it simple by

[COLOR=#333333]' 1) loop through open books and look for the destination book (name contains "Dump")[/COLOR]
    For iIdx = 1 To Workbooks.Count 'assumes won't have >30000 workbooks open so idx, interger variable takes up less space wb variable    If InStr(1, UCase(Workbooks(iIdx).Name), "DUMP") > 0 Then      Set wbSource = Workbooks(iIdx) 'referencing wb within index of workbooks      Set wsSource = wbSource.Sheets(1)            On Error Resume Next 'if an error is generated, don't pop up messages to the user, just resume next, keep on trucking      On Error GoTo 0 'do regular error junk, set      Exit For 'will take the first wb with "process", once found it works and exits    End If 
[COLOR=#333333]  Next iIdx
[/COLOR][COLOR=#333333]Msg box "File not Found"
[/COLOR]

If it gets to that stage the the file isn't found..

 

[trillium]

Hi Blunder
Thanks for the idea, but I think due to the error handling it doesn't work. If the DUMP file is open, it still returns the message box.

 

[Blunder1]

this should work then if there is code after..

' 1) loop through open books and look for the destination book (name contains "Dump")
  
  For iIdx = 1 To Workbooks.Count 'assumes won't have >30000 workbooks open so idx, interger variable takes up less space wb variable
    If InStr(1, UCase(Workbooks(iIdx).Name), "DUMP") > 0 Then
      Set wbSource = Workbooks(iIdx) 'referencing wb within index of workbooks
      Set wsSource = wbSource.Sheets(1)
      
      On Error Resume Next 'if an error is generated, don't pop up messages to the user, just resume next, keep on trucking
 On Error Resume Next 'do regular error junk, set
      Exit For 'will take the first wb with "process", once found it works and exits
    End If
  Next iIdx
On Error Resume Next
If wbSource Is Nothing Then
MsgBox = "No file found"
Else
MsgBox = "File found"
End If
On Error Resume Next

 

[trillium]

THANK YOU! Works great! Just one little correction to your code. Have to remove the = after MsgBox because if it's included, it thinks it needs to return a Variant or Object.

On Error Resume Next
If wbSource Is Nothing Then
MsgBox "No file found"
Else
MsgBox "File found"
End If
On Error Resume Next