Closing Excel Window that's open VBA

Question

I have code that checks if an excel file exists. If the file already exists then I want to append to the document. But the user might already have the excel file open and in the foreground. How can I close that excel file window? (It would be the equivalent of hitting the x in the excel file's window)

I've tried the following:

XLName = "excel2close.xlxs"
'Set oxlApp = CreateObject("Excel.Application") 'EDIT: Not correct
'Set oxlApp = GetObject(", Excel.Application")  'EDIT: Syntax error
'Set oxlApp = GetObject(XLName, Excel.Application)  'EDIT: Error 
'429': ActiveX component can't create object

Set oxlApp = GetObject(XLName, "Excel.Application") 'EDIT: Error
'432': File name or class name not found during Automation operation

'Returns Run-time error '9': Subscript out of range
oxlApp.Workbooks(XLName).Close SaveChanges:=False

'Run-time error '438': Object doesn't support this property method
oxlApp.Workbook(XLName).Close

'Run-time error '438': Object doesn't support this property method
oxlApp.Window(XLName).Close

'Returns Run-time error '9': Subscript out of range
oxlApp.Windows(XLName).Close

I've also tried all of these with the full path, not just the name.

Answer 1

Please, test the next code and try understanding it:

Sub ExcelSessions()
   Dim XLName As String, oxlApp As Object, wb As Workbook, wbOp As Workbook
   XLName = "excel2close.xlsx" 'be careful, you used an inexisting extension (xlxs)...
   
   On Error Resume Next
    Set oxlApp = GetObject(, "Excel.Application") 'if an existing open session exists, it will be set in this way
    If err.Number <> 0 Then   'If no open session exists:
        err.Clear: On Error GoTo 0
        Set oxlApp = CreateObject("Excel.Application") 'create a new session
    Else                      'If an existing session could be set, check if the necessary workbook exists:
        For Each wb In oxlApp.Workbooks
            If wb.Name = XLName Then Set wbOp = wb: Exit For 'Only if it exists, it is set
        Next
    End If
  On Error GoTo 0
  If Not wbOp Is Nothing Then  'if the necessary workbook has been found:
       wbOp.Close              'it is closed
  Else
        MsgBox "No document named """ & XLName & """ exists in the found open session."
  End If
End Sub

Now, GetObject will set the first found session. But if more than one session exist, the workbook you search for may not be found in the found open session...

In such a case there are two options to be explored:

1. If you are sure that the workbook is open and you know its full path, you can obtain the necessary session in the next way:

Sub getExcelSessByWbFullName()
   Dim XLName As String, oxlApp As Object, wb As Workbook
    
     XLName = "full name of  excel2close.xlsx" '"C:\yourFolder\excel2close.xlsx"
    Set oxlApp = GetObject(XLName).Application
    oxlApp.Workbooks(Right(XLName, InStrRev(XLName, "\"))).Close
    'oxlApp.Quit 'if you want quiting the session you must uncomment this line
End Sub

2. If you only think it may be open and also many session may be open I can show you a more complicated method to found all open sessions and check in each if them if the searched workbook is open. If open, close it (and the session, too, if you want that). But I will post it after you test the previous codes and prove that you understand what they are doing...