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
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
