INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Write file date to table after importing VBA

Write file date to table after importing VBA

(OP)
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

RE: Write file date to table after importing VBA

Hint:

CODE

For intFile = 1 To UBound(strFileList)
    strFullPath = strPath & strFileList(intFile)
    'DoCmd.TransferSpreadsheet acImport, _
    ' acSpreadsheetTypeExcel97, strcTableName, _
    'strFullPath, True
    
    DoCmd.TransferText acImportDelim, , strcTableName, strFullPath, True, ""
    
    MsgBox FileDateTime(strFullPath)
    
    '"UPDATE " & strcTableName & " SET DateField = #" & FileDateTime(strFullPath) & "#"
    'Kill strFullPath
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close