VBA to check if file is open and if not advise user to open the file before moving on

[Peter Davison]

I have this code (as part of a larger code) for checking if a specific file is open.
The only problem I have is that even though the file is open it tries to open it again and then crashes.
Any thoughts why it tries to open the file again?
Appreciate any help on this one.

'declare variables
Dim wb As Workbook
Dim FilePath As String
FilePath = "C:\cluster insight macro\Breakfast Foods Combined Raw Data.xlsm"
For Each wb In Workbooks
If wb.Name = "Breakfast Foods Combined Raw Data.xlsm" Then
'this message will appear if the open workbooks is Breakfast Foods Combined Raw Data.xlsm
MsgBox "Main Data File is Open"
returnval = "fileOpen"
End If
Next

If returnval <> "fopen" Then
Workbooks.Open FilePath, UpdateLinks:=0
End If

 

[Joe4]

Your values don't match:

returnval = "fileOpen"
and
If returnval <> "fopen" Then

 

[Peter Davison]

I did change that but it still was causing a problem.
Can you see anything else wrong with the code?

 

[Joe4]

I did change that but it still was causing a problem.
Can you see anything else wrong with the code?

Those two values have to match, as it can never equal something that you aren't setting it equal to.

Also note that the workbooks MUST be in the same Excel session on your computer (if they are opened in different Excel sessions, the one file won't see files in the other session).
This often happens when opening files directly from Windows Explorer.

You can easily confirm if it is seeing the file by adding a MsgBox inside your loop returning the name of each file as it loops through it, i.e.

MsgBox wb.Name

Also note, if you don't want to macro to do anything if the file is already open, instead of setting the value equal to a variable, just exit the the whole procedure with an:

Exit Sub

line.

 

[Peter Davison]

Hi Again,
I'm trying a new approach and doing the check as one Sub
However it doesn't like IsWorkBookOpen?
Is that because I am on 365 and IsWorkBookOpen old code
I've also added the option that if the file is open then the user can call the next sub.

Does it all look okay below except for the IsWorkBookOpen

Thank you for your help.

Sub CheckFileOpen()

Dim Ret As Long
Dim answer As Integer

Ret = IsWorkBookOpen("C:\cluster insight macro\Breakfast Foods Combined Raw Data.xlsm")

If Ret = True Then
MsgBox "File is Open"

'Set Message
answer = MsgBox("Do you want to run the Copy Data?", vbQuestion + vbYesNo + vbDefaultButton2, "Macro Analysis")
If answer = vbYes Then
Call CopyData

Else
MsgBox "File is Closed please open the file - C:\cluster insight macro\Breakfast Foods Combined Raw Data.xlsm"
End If


End Sub

 

[Joe4]

That is because "IsWorkBookOpen" is NOT a native VBA function. It is a User Defined Function (which means, you need to specifically design/code it).
Here is a link that has the function which you can copy and shows you how to use it:

How to quickly check if a file (workbook) is open or closed in Excel?

ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.

www.extendoffice.com

 

[Peter Davison]

Thankyou for your help

 

[Joe4]

You are welcome.
Were you able to get it to work using the information in that link?