reading the array to import the files in access 2010

HI..hopefully someone is able to help me with this.. i am trying to import files based on modified date. any files that were modified yesterday's date from today's date, should be picked up and index in an array. i want to then extract the file and the name to create table (multiple maybe) to track the files and number of records in the file. also add a column to add the file name and the date. the file format is name_date_#of records.csv (myfile_yyyymmdd_0.csv). there are 2 problems that i am having. 1) i was having problem picking up the right modified date. it will pick random files 2) I don't know how to read the array string of the file object to import into an access data base table and read the name of the file to update new column with the name and modified date



i have the program on the text file. please HELP!



below is the code:

Function ImportFiles()



Dim FSO As scripting.FileSystemObject

Dim SourceFolder As scripting.Folder

Dim FileItem As scripting.File

Dim varDate As Variant

Dim sFile As String

Dim sFileList() As String

Dim intFile As Integer

Dim sPath As String

Dim sOutFile As String



Const sFolder = "C:\DATA\"

Const ImportSpec = "Apps"



Set FSO = New scripting.FileSystemObject

Set SourceFolder = FSO.GetFolder(sFolder)





varDate = Format(Now() - 1, "mm/dd/yyyy")

sFile = Dir(sFolder & "*.csv")



For Each FileItem In SourceFolder.Files

'varDate = FileItem.DateLastModified

If FileItem.DateLastModified > varDate Then

If FileItem.DateLastModified < Date Then

varDate = FileItem.DateLastModified

sFile = FileItem.Name

intFile = intFile + 1

ReDim Preserve sFileList(1 To intFile)

sFileList(intFile) = sFile

sFile = Dir()

End If

End If

Next FileItem



If intFile = 0 Then

MsgBox sFolder & vbNewLine & vbNewLine _

& "there are no files for & varDate " & vbOKOnly



GoTo Exit_ImportFiles



End If



For intFile = 1 To UBound(sFileList)



sPath = sFolder & sFileList(intFile)



If FileItem.Name = "myfile" & "*.csv" Then

DoCmd.DeleteObject acTable, "tblmyfile"

DoCmd.TransferText acImportDelim, ImportSpec, _

"tblmyfile", sPath, True

End If



Next intFile

MsgBox UBound(sFileList) & " file(s) were imported", vbOKOnly



Exit_ImportFiles:



End Function

reading the array to import the files in access 2010