VBA to check if file is open

Question

I have the following code which is supposed to check if a file is open or not. If the file is open then it should do nothing but if the file isn't open then it should display a message saying that the file isn't open and exit the sub.

However, it always exits the regardless if the file is open or not.

Sub Update_Folder()

Dim str As String
Dim openPos As Integer
Dim closePos As Integer
Dim midBit As String





'Creates file string name
str = Range("H6").Value
openPos = InStr(str, "[")
closePos = InStr(str, "]")
midBit = Mid(str, openPos + 1, closePos - openPos - 1)


   If Range("B2").Value = "Yes" Or Range("B4").Value = "Yes" Or Range("B5").Value = "Yes" Or Range("B6").Value = "Yes" Or Range("B9").Value = "Yes" Or Range("B12").Value = "Yes" Or Range("B13").Value = "Yes" Then

On Error Resume Next
    Set WB = Workbooks(midBit)
    If Err Then MsgBox ("Please open the following file(s): " & str)
    'Exit Sub
On Error GoTo 0
 End If


Call Folder_Creation

END SUB

What is the correct syntax to check if a file is open and if so do nothing but if the file is not open display a msgbox and exit sub? Cell H6 is C:\Users\User1\Documents\[TestFile.xlsx].

I tried the solution from the following link but Detect whether Excel workbook is already open

It does not work for me. Maybe cause I am setting the path based on a file path in a cell? I get a file not found error when running it. The debug screen takes me to the Case Else: Error ErrNo line.

UPDATE:

The following code works for me. I had to change Error GoTo syntax and then move error handler to the end of the script. So if the error occurs it skips to the bottom of the script

Sub Update_Folder()

Dim str As String
Dim openPos As Integer
Dim closePos As Integer
Dim midBit As String

'Creates file string name
str = Range("H6").Value
openPos = InStr(str, "[")
closePos = InStr(str, "]")
midBit = Mid(str, openPos + 1, closePos - openPos - 1)


   If Range("B2").Value = "Yes" Or Range("B4").Value = "Yes" Or Range("B5").Value = "Yes" Or Range("B6").Value = "Yes" Or Range("B9").Value = "Yes" Or Range("B12").Value = "Yes" Or Range("B13").Value = "Yes" Then

On Error goto ErrorHandler1:
    Set WB = Workbooks(midBit)
    If Err Then MsgBox ("Please open the following file(s): " & str)
    'Exit Sub



Call Folder_Creation


 ErrorHandler1:

MsgBox ("Please open the following file(s): " & str)
    Exit Sub



END SUB

What kind of file? A workbook? – Pierre44 Oct 25 '18 at 13:47 — Pierre44, Oct 25 '18 at 13:47
What string does midbit return? Can you show it here? – Pierre44 Oct 25 '18 at 15:03 — Pierre44, Oct 25 '18 at 15:03

Thaer A · Answer 1 · 2018-10-25T14:38:35.693

0

Maybe this will be of help:

Macro code to check whether a file is already open

https://support.microsoft.com/en-us/help/291295/macro-code-to-check-whether-a-file-is-already-open

Follow the instructionsin the link above. Which are as follows: In Excel, Press Alt+F11 to bring up VBA. Click Insert > Module. Copy and paste the code. Go back to Excel and set up the cells as in the picture below. This is how your cells should be setup:

edited Oct 25 '18 at 14:38

answered Oct 25 '18 at 13:47

Thaer A

2,243
1
10
14

I have tried that and it does not work for me. Cell `H6` contains the file path and when I dim it as a string(str) to be called as ` If IsFileOpen(str) Then` based on the link you shared, I get an error. The error iget is either file not found or I don't have access which I do have access. Also the `debug` screen takes me to the `Case Else Error errnum` section. – AlmostThere Oct 25 '18 at 13:57
@AlmostThere I don't get what you're saying. You don't need to dim anything as a string. Press Alt+F11. Click Insert > Module. Copy and paste the code. Go back to Excel and set up the cells as in the picture above. – Thaer A Oct 25 '18 at 14:36

VBA to check if file is open

1 Answers1