Testing if a file is open

  • Thread starter Thread starter John Baker
  • Start date Start date
J

John Baker

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
 
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,

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
 
Back
Top