Link to home
Create AccountLog in
Avatar of lkirke
lkirkeFlag for Australia

asked on

Importing Text File (Different Date and Time Stamps)

Hello Experts,

Have the attached code that works perfectly.

However, I was just informed that the file name will change from EMP Upload File.txt to the format of EMP Upload File_ddMMyyyy hhmmss.txt.

How do I adjust the code to import this considering the time and dates stamps will always change?



P.S. Happy New Year. :)
Private Sub cmd_Import_EMP_File_Click()

DoCmd.SetWarnings False
DoCmd.CopyObject , "tbl_EMP_Upload_Temp", acTable, "tbl_EMP_Upload"
DoCmd.DeleteObject acTable, "temp_tbl_EMP_Upload_File"
DoCmd.TransferText acImportDelim, "EMP_Upload_File_Import_Specification", "temp_tbl_EMP_Upload_File", "C:\User\EMP Upload File.txt", True, ""
DoCmd.CopyObject , "tbl_EMP_Upload", acTable, "temp_tbl_EMP_Upload_File"
DoCmd.OpenQuery ("qry_EMP_Upload_Comparison_Append")
DoCmd.OpenQuery ("qry_EMP_Upload_CurrentBalance_Make")
DoCmd.OpenQuery ("qry_EMP_Upload_CurrentBalance_Update")
MsgBox "SUCCESS - EMP Import File Uploaded into Database", vbInformation, "EMP Monthly Update File Uploaded!"
DoCmd.SetWarnings True

End Sub

Open in new window

Avatar of Usama Foad
Usama Foad
Flag of Egypt image

I think you need something like this:

First Code

DoCmd.TransferText acImportDelim, "EMP_Upload_File_Import_Specification", "temp_tbl_EMP_Upload_File", 
"C:\User\EMP Upload File_" & Format(Now, "dd-MM-yyyy hh:mm:ss") & ".txt", True, ""

Open in new window


Second Code

DoCmd.TransferText acImportDelim, "EMP_Upload_File_Import_Specification", "temp_tbl_EMP_Upload_File",
 "C:\User\EMP Upload File_" & Format(Now, "ddMMyyyy hhmmss") & ".txt", True, ""

Open in new window

The results will be like:

1. First Code

EMP Upload File_31-12-2010 09:25:06.txt

2. Second Code

EMP Upload File_31122010 092504.txt
You need to replace "Now" with string or variable that hold the right DateTime value you want (may be you need to retrieve this information from input box, txt box or something like that).
You need to know the separator used with date is it "-", "_", ":" …etc
I hope this will help you.
Avatar of als315
If you have only one file, it is better to rename it before import:
ren "EMP Upload File*.txt" "EMP Upload File.txt"
If you have more files then one, you can try to use example fro my answer to this question:
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of lkirke


Thank you Experts. All great suggestions.