MS Access VBA to delete files in Windows Explorer

I am using Access 2010 and Windows 7

I want to programmatically delete files that are more than 10 days old from a folder using MS Access VBA. I am using the filesystemobject but I am either not coding it correctly or am missing a reference library. I have not even put in the date compare code because I can't seem to get the date created. I get a file not found error on the dteCreate = fileOb.GetFile(fileNme).DateCreated line.

Dim fileOb As Object
Dim fileNme As String
Dim path As String
Dim dteCreate As String
Dim FileGet As String

Set fileOb = CreateObject("Scripting.FileSystemObject")
path = "\\Cust\DeptFiles$\pol\Shop\TopCust\"
fileNme = Dir(path)
Do While (fileNme <> "")
      If InStr(fileNme, "RptRecentContact") > 0 Then
          FileGet = fileOb.GetFileName(fileNme)
          dteCreate = fileOb.GetFile(fileNme).DateCreated      
'           Kill fileName
'         End If
      End If

Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this simple function

Function getFileInfo(path)
   Dim fso, f, msg
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set f = fso.GetFile(path)
   msg = f.Path & "<br>"
   msg = msg & "Created: " & f.DateCreated & "<br>"
   msg = msg & "Last Accessed: " & f.DateLastAccessed & "<br>"
   msg = msg & "Last Modified: " & f.DateLastModified  
   getFileInfo = msg
End Function
Rey Obrero (Capricorn1)Commented:
FileGet = fileOb.GetFileName(fileNme)
          dteCreate = FileGet.DateCreated  

to delete the file you need to use

       fileOb.deletefile fileGet
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:

Kill fileNme


Kill fileName

Note that you have dropped the a from Name in your other code.

I would highly recommend using

Option Explicit

at the top of your code modules to avid any spelling mistakes like this.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.