Test for Open File

[Guest]

Hi all,

I need to create a If statment that tests if a Xls file is open or not.
Any ideas?

Thanks in advance.
Aaron

 

[Tom Ogilvy]

On error resume next
set bk = workbooks("MyBook.xls")
On error goto 0
if not bk is nothing then
msgbox "MyBook.xls is already open in excel"
else
msgbox "MyBook.xls is not open"
End if

if you mean located on a shared drive and being used by someone else:




http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=184982
WD97: VBA Function to Check If File or Document Is Open

 

[Guest]

Hi Tom,

I cannot get this to work.
It may be because I am using the Macro in workbook1 and need it to look at
workbook2 and if open then run code. Would this make a difference?

Thanks,
Aaron

 

[Dave Peterson]

First, Tom offered two suggestions.

Which one did you try?
And which one did you really mean to try?

You may want to post the code that you tried, too.

And one more question...

If workbook1.xls is open in one instance of excel and workbook2.xls is open in
another instance (not just different windows of the same instance), then you'll
want to use Tom's second suggestion.

 

[Guest]

Hi Dave,

I used the first one because I only wanted to know if I had the file open in
the same instance. The Error I am getting is when the file is not open and
is "object required", debug is pointing to "if not bk is nothing then".

Thanks,
Aaron

 

[Dave Peterson]

I added one line and it worked ok:

Dim bk As Workbook
On Error Resume Next
Set bk = Workbooks("MyBook.xls")
On Error GoTo 0
If Not bk Is Nothing Then
MsgBox "MyBook.xls is already open in excel"
Else
MsgBox "MyBook.xls is not open"
End If

 

[Guest]

Prefect! That Dave and Tom!!

I added one line and it worked ok:

Dim bk As Workbook
On Error Resume Next
Set bk = Workbooks("MyBook.xls")
On Error GoTo 0
If Not bk Is Nothing Then
MsgBox "MyBook.xls is already open in excel"
Else
MsgBox "MyBook.xls is not open"
End If