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

trillium

Board Regular
Joined
Aug 9, 2010
Messages
63
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:

Code:
' 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?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Blunder1

Active Member
Joined
Jun 2, 2010
Messages
250
you could keep it simple by

Code:
[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..
 
Upvote 0

trillium

Board Regular
Joined
Aug 9, 2010
Messages
63
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.
 
Upvote 0

Blunder1

Active Member
Joined
Jun 2, 2010
Messages
250
this should work then if there is code after..
Code:
' 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
 
Upvote 0

trillium

Board Regular
Joined
Aug 9, 2010
Messages
63
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.

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

Forum statistics

Threads
1,191,625
Messages
5,987,749
Members
440,106
Latest member
davcurnutt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top