Check To see if file is open

[Mark]

I have a form that tracks representative info and when "submitted" opens
another file and passes the data. There will be multiple users for the form,
and I want to have it setup so if 2 people hit submit at the same time, it
doesn't crash on one person with the data file open in a read only mode.

Workbooks.Open Filename:= _
"\\c:\Floor Support Question Logs\Question Log.csv",
WriteResPassword:="askme", IgnoreReadOnlyRecommended:=True

Sheets("question log").Select
Range("A1").Select

How would you check to see if the file is already open and if it is, loop
until it was closed before proceeding?

Thanks!

 

[Mike H]

Mark,

To check if a workbook is open try this

Sub IsitOpen()
Dim WB As Workbook
On Error Resume Next
Set WB = Workbooks("mybook.xls")
If WB Is Nothing Then
MsgBox "Workbook is not open"
Set wBook = Nothing
On Error GoTo 0
Else
MsgBox "Yes it is open"
Set wBook = Nothing
On Error GoTo 0
End If
End Sub

Looping until it's closed is effectively putting your code into an endless
loop because there is no way of knowing fro how long the 'other' user
willhave it open so I would suggest you simply terminate your code with a
suitable message if the workbook os open.

Mike