atomtm
11-08-2016, 02:34 AM
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
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