Close excel file if open

[atomtm]

Hello !
I would like to check if a file is open and close it if so !
I am using this function:

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

Sub test()
If Not IsFileOpen("C:\MyTest\volker2.xls") Then
Workbooks.Open "C:\MyTest\volker2.xls"
End If
End Sub
To close the file if open I use this :

If IsFileOpen(filePath) = True Then
Dim tmpWkb As Workbook
Set tmpWkb = Workbooks(filePath)
tmpWkb.Close SaveChanges:=True

However I am getting subscript out of range error . How can I close the file without errors?

Thank you in advance

[Kenneth Hobs]

You can not close a file open by someone else so I don't see the point. That sort of code was only meant to let you know that it was open by someone else.

To check your own workbooks:

Sub Test_IsWorkbookOPen()
MsgBox IsWorkbookOpen("Personal.xls"), , "Personal.xls Open?"
MsgBox IsWorkbookOpen("Personal.xlsb"), , "Personal.xlsb Open?"
MsgBox IsWorkbookOpen("Personalx.xlsb"), , "Personalx.xlsb Open?"
End Sub


Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function

[atomtm]

You can not close a file open by someone else so I don't see the point. That sort of code was only meant to let you know that it was open by someone else.

To check your own workbooks:

Sub Test_IsWorkbookOPen()
MsgBox IsWorkbookOpen("Personal.xls"), , "Personal.xls Open?"
MsgBox IsWorkbookOpen("Personal.xlsb"), , "Personal.xlsb Open?"
MsgBox IsWorkbookOpen("Personalx.xlsb"), , "Personalx.xlsb Open?"
End Sub


Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function

This seems to work :


If IsFileOpen(filePath) = True Then
Dim tmpWkb As Workbook
Set tmpWkb = Workbooks(fileName)
tmpWkb.Close SaveChanges:=True

The problem was on
Set tmpWkb = Workbooks(filePath) replaced with
Set tmpWkb = Workbooks(fileName) where fileName is not the full path to the file but only its name
Appreciate your response !