asked on

Access VBA - File Scripting Object

Need help with the File Scripting Object.
Since Microsoft has decided to do away with the File Search Object, I need to re-write a couple of modules using the File Scripting Object. I have made a few attempts to no avail.

Here is what I need to accomplish:
Basically look in five folders to see if any files exist, if so, then Delete the files it finds.
The main folder for the five sub folders is C:\Data\P4DL\InvReports\
The five sub folders I need to loop thru looking for files that may exist and then delete (files only)are:
      InvRpts_DIR
      InvRpts_eFile
      Inv_Rpts_Fax
      InvRpts_FedEx
      InvRpts_USMail

Patrick Matthews

Are you deleting all files found there, or just certain files?If only certain files, what criteria determine whether to delete them?

djMcCauley

ASKER

Need to delete all the txt files found in each of the five folders listed.

djMcCauley

ASKER

by the way, .txt files will be the only type of file found in these folders.

Patrick Matthews

Try this...

Sub KillThemAll()

    Dim fso As Object

    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.DeleteFile "C:\Data\P4DL\InvReports\InvRpts_DIR\*.txt"
    fso.DeleteFile "C:\Data\P4DL\InvReports\InvRpts_eFile\*.txt"
    fso.DeleteFile "C:\Data\P4DL\InvReports\Inv_Rpts_Fax\*.txt"
    fso.DeleteFile "C:\Data\P4DL\InvReports\InvRpts_FedEx\*.txt"
    fso.DeleteFile "C:\Data\P4DL\InvReports\InvRpts_USMail\*.txt"

End Sub

Open in new window

Patrick Matthews

Cleaner...

Sub KillThemAll()

    Dim fso As Object

    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.DeleteFile "C:\Data\P4DL\InvReports\InvRpts_DIR\*.txt"
    fso.DeleteFile "C:\Data\P4DL\InvReports\InvRpts_eFile\*.txt"
    fso.DeleteFile "C:\Data\P4DL\InvReports\Inv_Rpts_Fax\*.txt"
    fso.DeleteFile "C:\Data\P4DL\InvReports\InvRpts_FedEx\*.txt"
    fso.DeleteFile "C:\Data\P4DL\InvReports\InvRpts_USMail\*.txt"

    Set fso = Nothing

End Sub

Open in new window

rockiroads

What about using the Dir and Kill object instead?

A sample procedure provided. Pass in the full path to the directory and the suffix to find
eg

DeleteFiles "c:\temp\", "*.txt"

To test it picks up the right files in your dirs, comment out Kill then look in immediate window for the "deleting ..." lines

You need to call it for each dir you want to delete files from

Public Sub DeleteFiles(ByVal sDir As String, ByVal sSuffix As String)

    Dim sFile As String
    
    
    If right$(sDir, 1) <> "\" Then sDir = sDir & "\"
    sFile = Dir$(sDir & sSuffix)
    Do While sFile <> ""
        Debug.Print "deleting " & sDir & sFile
        kill sDir & sFile
        sFile = Dir
    Loop
    
End Sub

Open in new window

djMcCauley

ASKER

The KillThemAll works if files exist, but errors out if the folder happens to be empty.
This is the one I would like to use if possible.

rockiroads

What about adding a check still using dir then

dir will return the first filename. if nothing returned then no files

so call this

DeleteFilesFSO "C:\Data\P4DL\InvReports\InvRpts_DIR\*.txt"

Public Sub DeleteFilesFSO(ByVal sDir As String)

    Dim fso As object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Dir(sDir) <> "" Then
        fso.DeleteFile sDir, True
    End If
    Set fso = Nothing
End Sub

Open in new window

rockiroads

fso has a method called FileExists but it didnt seem to work with wildcards

if fso.fileexists(sDir) then

seems to work with one filename only

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockiroads

lol, thats cheating Patrick. Won't you hide genuine errors?

djMcCauley

ASKER

The code works, what errors would it hide that would matter?

Patrick Matthews

rockiroads,Yes, and no. :)Part of the problem is that DeleteFile throws an error if there are no files matching that name, but that may not be an "error" per se so much as it means there were simply no files to delete.Now, you are right that this can obscure a bigger problem, such as the user not having the proper permissions to access the directory or delete the files...Patrick

djMcCauley

ASKER

I just want to delete all files that it finds, if no files are found just keep moving ahead too the next folder.

rockiroads

code works yes. so if you expected the directory to be there and it was not accessible for whatever reason, you wont know

slight tweak to fso solution would be to use FolderExists method

djMcCauley

ASKER

I would also like to thank rockiroads for his advice.

rockiroads

djMcCauley, Patrick's solution will work fine so as long as for this kind of operation I don't envisage other problems occurring. This looks like a straight delete from fixed directories problem.

What is the process of these txt files. If some do get left behind (which shouldnt happen), will it affect any of your processing? If just temp files then there is no problem.

djMcCauley

ASKER

This is an end of the month process, that gets run on the 1st of each month. If any reports are found in these folders when this database is run, then they are from last month and need to be deleted before the process runs for the new month.

rockiroads

Thats fine. Its down to personal choice really. I prefer to let it throw an error if a failure does occur especially if the odd txt file was left and it could have an impact on future processing. Obviously to bypass no files error, it was just a simple case of checking files existed before performing the delete. But question done and dusted now.