VBA Question - Checking if file is open before opening it

[jphanton00]

I need VBA code to test if a file in a directory is open before opening it. If the file is open then I want a message box to pop up letting the user know its open. Once the user clicks OK, then I want it to skip the next set of steps and move on to attempting to open the next file. If the file is not open then I want it to proceed with the next set of steps.

I already have a macro written that I would like to insert this code into. I can't figure out how to attach the code I currently have (its rather lengthy) to give you an idea of where I need it and what comes next. But the just of what I'm already doing is consolidating new entries on multiple files into one file. The other day someone was in one of the files I was trying to consolidate and it hung up my macro. So I just want it to bypass attempting to open it and move on to the next one.

I've already spent about 4 hours reasearching this and trying different codes, but nothing is working.

Thanks in advance for your help!!

 

[jphanton00]

Thank you, but this still isn't answering my questions. I don't see it referencing a path in the function or a message box or how it would end and go to the next file as was indicated in the piece of the macro I pasted in the prvious thread.

 

[jphanton00]

I figured it out. The code goes like this:

Function FileLocked(strFileName As String) As Boolean
On Error Resume Next
Open strFileName For Binary Access Read Write Lock Read Write As #1
Close #1
If Err.Number <> 0 Then
FileLocked = True
Err.Clear
End If
End Function

Sub Upload()

Dim Foremost As String
Dim QA As String
Dim Directory As String
Dim strFileName As String

QA = "Quality Assurance WQA DB 2012.xlsm"
Foremost = "Foremost WQA DB 2012.xlsm"
Directory = "\\pafnp0628\Accounting\00000 Management Reports\QA Reviews\2012\"

strFileName = Directory & QA
If Not FileLocked(strFileName) Then
Workbooks.Open Directory & QA

'List steps to perform functions to QA file if file is not open by another user
XXX
XXX
XXX
XXX

If strFileName = Directory & QA Then
GoTo 1
Else
End If

End If

MsgBox "The Quality Assurance WQA file is in use. Please upload at a later time."

'Next file

1
strFileName = Directory & Foremost
If Not FileLocked(strFileName) Then
Workbooks.Open Directory & Foremost

'List steps to perform functions to Foremost file if file is not open by another user
XXX
XXX
XXX
XXX

If strFileName = Directory & Foremost Then
GoTo 2
Else
End If

End If

MsgBox "The Foremost WQA file is in use. Please upload at a later time."

'Next file and so on
XXX
XXX
XXX
XXX

End Sub

So the above code will test to see if a file is open before attempting to open it. If its open it clears the error and moves to the next step which is a message box letting the user know the file is in use. It then moves on to check the next file. If the file is not open already then it continues on with the next set of code, bypasses the "file in use" message box and moves on to check the next file. Several hours of reasearch later and voilà!