Write file date to table after importing VBA

[talliaman]

I have the below code that copies .csv files into Access. I would like Access to read the file create date from the .csv files properties and write it to the table that I am importing into. I am not fluent in VBA and the code below was not written by me so I need help. Can this be done easily?

Private Sub Command29_Click()
Const strcPath As String = _
"Y:\SHARE\BPO\Elg_File_Reports\Elg_File_Reports"
Const strcTableName As String = "2013 elg file totals report"
Dim strPath As String
Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer
Dim strFullPath As String

If Right(strcPath, 1) = "\" Then
strPath = strcPath
Else
strPath = strcPath & "\"
End If

strFile = Dir(strPath & "*.csv")
While strFile <> ""
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
If intFile = 0 Then
MsgBox strcPath & vbNewLine & vbNewLine _
& "The above directory contains no CSV files.", _
vbExclamation + vbOKOnly, "Program Finished"
GoTo Exit_Import_From_Excel
End If

For intFile = 1 To UBound(strFileList)
strFullPath = strPath & strFileList(intFile)
'DoCmd.TransferSpreadsheet acImport, _
' acSpreadsheetTypeExcel97, strcTableName, _
'strFullPath, True

DoCmd.TransferText acImportDelim, , strcTableName, strFullPath, True, ""
'Kill strFullPath

Next

MsgBox UBound(strFileList) & " file(s) were imported", _
vbOKOnly + vbInformation, "Program Finished"

Exit_Import_From_Excel:
'DoCmd.RunSQL "UPDATE [2013 Elg File Totals Report] SET [2013 Elg File Totals Report].DIVISION = 'GALLAGHER'WHERE ((([2013 Elg File Totals Report].DIVISION) Is Null))"

Exit Sub

End Sub

 

[SkipVought]

Forum705.

 

[Andrzejek]

Hint:

For intFile = 1 To UBound(strFileList)
    strFullPath = strPath & strFileList(intFile)[green]
    'DoCmd.TransferSpreadsheet acImport, _
    ' acSpreadsheetTypeExcel97, strcTableName, _
    'strFullPath, True[/green]
    
    DoCmd.TransferText acImportDelim, , strcTableName, strFullPath, True, ""
[blue]    
    MsgBox FileDateTime(strFullPath)
[/blue][green]    
    '"UPDATE " & strcTableName & " SET DateField = #" & FileDateTime(strFullPath) & "#"
    'Kill strFullPath[/green]
Next

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.