Close an open Microsoft Excel file from Microsoft Access

Let's say you need to delete or replace a certain Microsoft Excel file from a Microsoft Access application. 

Many times Microsoft Access need to report in Microsoft Excel files, so you need to generate them. But once a Microsoft Excel file was generated and even opened, you may loose control on that file. The user can modify it, and let it opened without saving it and after a while the user may need to see a fresh report in that file. So, Microsoft Access need to generate again that certain file, but is not that simple.

First, Microsoft Access need to see if that Excel file is opened.

Then Microsoft Access need to close it, without pending in a title bar blink and a message displayed, asking if you want to save any loaded files. Then it may need to delete it and generate it again with the new data.

All the manuals appeal to API functions. Below is how you may do it without them.

Sub ExcelGenerate()

Dim fso As Object

Dim strLoc As String

'choose the place where to generate the file:

strLoc = Application.CurrentProject.Path

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(strLoc & "/SomeXLFile.xlsx") Then

   'see ActivateXLFile below

   If ActivateXLFile("SomeXLFile.xlsx") = True Then

      MsgBox "The Excel File is open and cannot be closed!", vbInformation

   Else

       fso.DeleteFile strLoc & "/SomeXLFile.xlsx"

       'then generate a new Microsoft Excel file

       '.....

   End If

Else

    'if the file doesn't exists, then there is no problem to generate it...

End If

End Sub

Public Function ActivateXLFile(DenFile As String) As Boolean

Dim appExcel As Excel.Workbook

ActivateXLFile = False 'it's False by default anyway, but is good to mention

On Error Resume Next

'asume that the file is open and try to activate it

AppActivate DenFile, False

If Err = 0 Then

   'if the file is open and activate, err is zero

   On Error GoTo Exi

   Set appExcel = GetObject(Application.CurrentProject.Path & "/" & DenFile)

   'functional version: Set appExcel = CreateObject(Application.CurrentProject.Path & "/" & DenFile)

   With appExcel

       .Save 'save the file; you don't need any blinking message from Excel, you will replace it, anyway...

       .Close

   End With

Else

    'if the file isn't open, it cannot be activate and close. Clear the error.

    Err.Clear

End If

Exit Function

Exi:

'just to be sure: if there anything happen and the file cannot be closed...

ActivateXLFile = True

End Function