djMcCauley
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
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
Are you deleting all files found there, or just certain files?If only certain files, what criteria determine whether to delete them?
ASKER
Need to delete all the txt files found in each of the five folders listed.
ASKER
by the way, .txt files will be the only type of file found in these folders.
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
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
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
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
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.
This is the one I would like to use if possible.
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\I nvRpts_DIR \*.txt"
dir will return the first filename. if nothing returned then no files
so call this
DeleteFilesFSO "C:\Data\P4DL\InvReports\I
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
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
if fso.fileexists(sDir) then
seems to work with one filename only
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
lol, thats cheating Patrick. Won't you hide genuine errors?
ASKER
The code works, what errors would it hide that would matter?
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
ASKER
I just want to delete all files that it finds, if no files are found just keep moving ahead too the next folder.
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
slight tweak to fso solution would be to use FolderExists method
ASKER
I would also like to thank rockiroads for his advice.
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.
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.
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.
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.