Hi,
I use a module to import multiple txt files at once... the problem is that i need to extract a portion of the filename for a column, Is there a way to import a file and at the same time extract part of the file name to populate a column?
All the files i'm working with have the following filename format:
FileNameMM-DD-YY.txt
ex.
XYZ07-19-10.txt
XYZ07-20-10.txt
XYZ07-21-10.txt
When importing them is there a way to extract the date portion and have it populate a date column within the table it loading to? Unfortunately the file lacks the date in the data itself.
Could anyone please me try making this work...
This is the current import module i'm using:
Public Sub subImport()
On Error GoTo Err_subImport
Dim stDocName As String
Dim fs As FileSearch
Dim ifn As String
Dim sql As String
Dim today As String
Dim fso As Scripting.FileSystemObject
Dim oktogo As Boolean
Dim specname As String
Dim repdate As String
Dim myfile As Scripting.TextStream
Dim i As Long
Dim y As Integer
Dim ShortFn As String
specname = "Import Specs"
'DoCmd.SetWarnings False
'sql = "INSERT INTO tbl_temp_Import"
'DoCmd.RunSQL sql 'Empty Temp Table
DoCmd.SetWarnings False
oktogo = False
ifn = CurrentProject.Path & "\Imports\"
Set fs = Application.FileSearch
With fs
.LookIn = ifn
.FileName = "*.txt"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
ShortFn = Right(.FoundFiles(i), Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))
DoCmd.TransferText acImportFixed, specname, "tbl_temp_Import", .FoundFiles(i), True
y = y + 1
Next i
Else
MsgBox "Please ensure that the source file is present and try again" & vbCr _
& "Required file location: " & vbCr & ifn, vbExclamation + vbOKOnly, "Input File Missing"
Exit Sub
End If
End With
MsgBox "Import complete. " & y & " files Imported", vbOKOnly + vbInformation, "Import Complete"
Exit_subImport:
' Turn warning messages back on
DoCmd.SetWarnings True
Exit Sub
Err_subImport:
MsgBox Err.Description
Resume Exit_subImport
End Sub