I need the correct syntax to delete an Excel file from a folder.

Set a reference to Microsoft Scripting Runtime and then
run this code

Dim fso As FileSystemObject
Set fso = New FileSystemObject
If fso.FileExists( CurrentProject.Path & "\" & "PROBLEM_LOG.xls") Then
    fso.DeleteFile ( CurrentProject.Path & "\" & "PROBLEM_LOG.xls")
End If

Select allOpen in new window

or you can simply use the KILL command.

if dir(strfilename) <> "" then KILL strFilename

Set a reference to Microsoft Scripting Runtime and then
run this code

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Select allOpen in new window

Or the late bound version (wich is more recommended)

Dim fso As Object        '// FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

Select allOpen in new window

I am not sure how to "Set a Reference Microsoft Run-Time Scripting"

This is the code of my function that is getting the error. This has been running fine for years. I don't understand why I'm getting the error now.

Public Function CreateProblemLog()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qMakeProblemLogTable"
DoCmd.OpenQuery "qCreateOeReportDueDate"
DoCmd.OpenQuery "qUpdateProblemLogMonthDayYear"
CurrentDb().TableDefs("ProblemLog").Fields("Count").Name = "Count towards PPM/ IPM (yes/no)"
CurrentDb().TableDefs("ProblemLog").Fields("QualitAlertNumber").Name = "Quality Alert # (if applicable)"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ProblemLog", CurrentProject.Path & "\" & "PROBLEM_LOG.xls", True
DoCmd.SetWarnings True
End Function

Select allOpen in new window

Why not go with the easy route?  as shown above

Well is not that hard
Just open your Access holding Shift key to bypass any startup code
Then hit ALT+F11 to get to VBE
Then from the menu
Tools-->References -->Microsoft Scripting Runtime
But reading that your code was working and now is not it seems that there is another problem...
For start try to change the path where you save the Excel ...something like "C:\Temp\problem.xls"
Give it a try a couple of times...if errors out again maybe something in your table is causing this issue (corrupt)
If not then probably you experience some kind of system locking that prevents the TransferSpreadsheet from working as it supposed to work.

@Dale:
Before deleting, we must be sure that the file exist, else an exception is thrown.
The best way to check for file existance is trough the file system library since the Dir function is unreliable.

On the other hand, we could handle the exception, but that's dirty. Unless no other choices, a function should not rely on exceptions handling to perform its job.

Fabrice,

The DIR() command takes care of the "file exists" issue.

if dir(strfilename) <> "" then 
    KILL strFilename 
end if

Select allOpen in new window

However, none of the solutions provided take into account that the file may be in use, so you will definitely need error handling to ensure that the if the file is in use, you properly handle that situation.

Dale

Simple scenario:

The Boss say: "I need to delete a file from a directory"
You answer the Boss: "No problem", and start coding a function.
Because you are smart, you write a generic function that you can reuse whenever you want:

Public Sub DeleteFile(ByVal FilePath As String)
        '// Delete the file if it exist
    If(Dir(FilePath ) <> vbNullString) Then
        Kill path
    End If
End Sub

Select allOpen in new window

Great, that work as expected.

Next, the Boss say: "I need to delete all dat files from a directory."
You answer the Boss: "No problem." and start coding.
Because you are smart, you still write a generic function, and reuse the previously written function:

Public Sub DeleteFromDirectory(ByVal DirectoryPath As String, ByVal FileExtension As String)
    Dim FullPath As String
    FullPath = Dir(DirectoryPath & "\*." & FileExtension)

        '// Browse the directory
    While (FullPath <> vbNullString)
            '// delete files with the corresponding extension
        DeleteFile DirectoryPath & "\" & FullPath
        FullPath = Dir
    Wend
End Sub

Select allOpen in new window

And now you tell the Boss: "Problem...."
(I let you figure out why).

Fabrice,

Sorry, don't have time for guessing games

The DeleteFromDirectory will delete at best one file, instead of all as expected.
This because the DeleteFile function update the Dir function's criteria.

Ehence why the Dir function is unreliable, and I can't stress enough about not using it anymore.

actually, you are getting the file name from the Dir command in DeleteFromDirectory, so you don't need the if statement in the DeleteFile procedure.

Guess you focused on the code and did not get the point.

The Dir function does not support cascading calls very well (my code is a simple illustration of this behavior), and there are no way to ensure such use case will never happen, the function can be used in code not written by you, or worst, in a compiled library (like an accde or whatever).

So, there is a flaw lurking here, and the only way to avoid bad surprises is to not use flawed functionalities in the first place.

I’ve never encountered a problem with Dir() and have been using it for years.   But you can get spurious results if you try to run it in a procedure and the run it again in a procedure called from the first.

You might consider using it to create an array of files matching your criteria, without the delete file call, then loop through the array to perform the delete operation

Dale where would I use that Kill command?

This database is not used by many people at the same time. In fact, normally it is used by individuals on their personal desktops and that's all. Simple would work fine.