Hi,
Thanks for any assistance in advance. I have VB code using the DoCmd.TransferText which imports multiple files and loads the data into a table. I would like to add part of the file name as a value in a given field in the table.
The table name is NS_Import2
The field name is NS_RouteFileName
The file names are e.g. A_190304.txt, B_190304.txt, C_190304.txt, etc...
I want to put A_190304, B_190304, and C_190304 as a value in the NS_RouteFileName field in the NS_Import2 table.
Here is my code:
Thanks for any assistance in advance. I have VB code using the DoCmd.TransferText which imports multiple files and loads the data into a table. I would like to add part of the file name as a value in a given field in the table.
The table name is NS_Import2
The field name is NS_RouteFileName
The file names are e.g. A_190304.txt, B_190304.txt, C_190304.txt, etc...
I want to put A_190304, B_190304, and C_190304 as a value in the NS_RouteFileName field in the NS_Import2 table.
Here is my code:
Code:
Private Sub btn_import_CSV_DblClick(Cancel As Integer)
DoCmd.SetWarnings (False)
On Error GoTo Err_FTP
Dim filepath As String
Dim strFile As String
Dim strPathFile As String
Dim user As String
user = Environ("username")
'################################################################################################
'Drop error tables
For Each tbl In CurrentDb.TableDefs
If InStr(tbl.Name, "Errors") > 0 Then
s = "DROP TABLE [" & tbl.Name & "]"
Debug.Print s
CurrentDb.Execute s
End If
Next
'################################################################################################
If user = "administrator" Then
filepath = "C:\Users" & user & "\OneDrive - Ryder\Desktop\OMS2\OMS\NorthStar\Import"
Else
filepath = "C:\Users" & user & "\OneDrive - Ryder\Desktop\OMS\NorthStar\Import"
End If
DoCmd.RunSQL "Delete * from NS_Import2"
strFile = Dir(filepath & "*.txt")
Do While Len(strFile) > 0
strPathFile = filepath & strFile
DoCmd.TransferText acImportDelim, "NS_DataImport", "NS_Import2", strPathFile, False
' Kill strPathFile
strFile = Dir()
Loop
MsgBox ("Data Imported!")
DoCmd.SetWarnings (True)
HandleExit:
Exit Sub
Err_FTP:
MsgBox Err.Description
Resume HandleExit
DoCmd.SetWarnings (True)
End Sub
Last edited: