Testing if a file is open
 

HI:

I would like to test if a specific file is open, and if it is I want to close it. Can
someone tell me the expression to use to deterine is a file (say collector.xls) is open?

Thank you

John Baker

Testing if a file is open
 

Hi John
use the following formula to check for an open workbook:
Function IsWorkbookOpen(Name As String)
Dim WB As Workbook
On Error Resume Next
Set WB = Workbooks(Name)
On Error GoTo 0
IsWorkbookOpen = Not (WB Is Nothing)
End Function

so you can then use the following
Sub Check_WB()
If IsWorkbookOpen("Book1.xls") Then
Workbooks("Book1.xls").Close
Else
Workbooks.Open Filename:="D:\temp\Book1.xls"
End If
End Sub

HTH
Frank

John Baker wrote:
HI:

I would like to test if a specific file is open, and if it is I want
to close it. Can someone tell me the expression to use to deterine is
a file (say collector.xls) is open?

Thank you

John Baker

Testing if a file is open
 

John,

Check the last function on this page at John Walkenbach's site:

http://j-walk.com/ss/excel/tips/tip54.htm

hth,

Doug

"John Baker" wrote in message
...
HI:

I would like to test if a specific file is open, and if it is I want to
close it. Can
someone tell me the expression to use to deterine is a file (say
collector.xls) is open?

Thank you

John Baker

Testing if a file is open
 

John,

Since you want to close the workbook if it is open, you can just
ignore the error if the workbook is not open. For example,

On Error Resume Next
Workbooks("Collector.xls").Close savechanges:=True 'or False
On Error Goto 0


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"John Baker" wrote in message
...
HI:

I would like to test if a specific file is open, and if it is I
want to close it. Can
someone tell me the expression to use to deterine is a file
(say collector.xls) is open?

Thank you

John Baker