Unanswered: Test for Existance of File Before Exporting from Access
My Access application in development exports to Excel using "OutputTo" and the user selects a sales officer from a form, then executes the export process and views the Excel file I'll call SALES_RESULTS.xls, which has been formatted from VBA in another Excel workbook. The problem occurs if Excel is not closed and the Excel file remains open, then the export is done again from the Access form. With the Excel file already open, Access can't do the export and produces the error message from "OutputTo", which happens to be in a macro.
I am looking for a solution in my Access code to test if the Excel file is open, then I would advise the user to close the file first before exporting a new file.
Using DIR does not find the open Excel file in this function:
filePath = "c:\Temp\"
fileName = "SALES_RESULTS.xls"
fileToDelete = filePath & fileName
If fileExists(fileToDelete, fileName) Then Kill fileToDelete
Function fileExists(pathFile As String, nameToCompare As String)
Dim fileIn As String
fileIn = UCase(Dir(pathFile))
If fileIn = UCase(nameToCompare) Then
fileExists = True
fileExists = False
Or maybe there is some other solution besides testing for an open Excel file.
If you google you will find several API solutions for this.
As an alternative, I usually time stamp my exports & check that the file does not exist on the system before saving. If it does then I uniquify the name. Not what you asked for but in case it is of use then you can see this in the Excel export code in the code-bank.
It seems that DoCmd.OutputTo acOutputQuery, ... does not obey the On Error statement when the file name to be exported to is currently open.
A solution that I found to work was to put code in the click event of the Report/Export button that attempts to write a blank line to the specific Excel file name, and when that fails, the user is told to close the Excel file first before creating a new one.
Thanks for your interest in this. It appears that I have resolved the open file problem.