Closing Excel file if it is already in open status when opening the same file second time using (VBA)

Question

I have a command button which opens the excel file.

But if the same file is opened for the second time it should first close it and then reopen it.

So i am looking for such code - I would be very thankful for this help.

Answer 1

Check if it is open, if not, close it using close method.

The following code was sourced from this VBAXpress Article

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 

If you just want to close a workbook without the user being prompted for any confirmations about saving the workbook you can simply do this :

ActiveWorkbook.Close False 

' closes the active workbook without saving any changes

ActiveWorkbook.Close True 

' closes the active workbook and saves any changes

ActiveWorkbook.Close 

' closes the active workbook and lets the user decide if changes are to be saved or not

Workbooks("BOOK1.XLS").Close SaveChanges:=False

Answer 2

Workbooks have a Close method.

See here