need help with import Module

[marubal21]

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

[dsmacs]

The following is what I use to import file/s from a location. It also stores the file name in table called tlkFileload



Sub importxt_click()

Dim MyFile, MyPath As Variant, MyName
Dim MyFileLoaded
Dim OpenTimeTm
Dim strSQL As String
Dim strFileSpec As String
Dim filedate as date

' On Error GoTo Err_proc
Me.Recalc

' Select the path where the files are located at

MyPath = CurrentProject.Path & "\Imports\"

'get a list of all the text files

strFileSpec = "*.txt"

MyName = Dir(MyPath & strFileSpec, vbNormal) ' Retrieve the first entry.

Do While MyName <> "" ' Start the loop.

'Create a table called tlkFileload with field = filename, type = txt and field = loaddate, type = date

'This checks to see if file has been loaded already or not
MyFileLoaded = DLookup("fileName", "tlkFileload", "fileName='" & MyName & "'")

If IsNull(MyFileLoaded) Then

'Clear any data from tbl_temp_Import
DoCmd.RunSQL ("DELETE tbl_temp_Import.* FROM tbl_temp_Import;")

' Import the text file

DoCmd.TransferText acImportFixed, specname, "tbl_temp_Import", MyPath & "\" & MyName, True

'You will need to include a impdate field in the tbl_temp_Import as set its type to date

'get the filedate from the file name

filedate = Format(CDate(Mid(Right(MyName,12),4,2) & '/' & Left(Right(myname,12),2) & "/20" & Mid(Right(MyName,12),7,2)),"MM/DD/YYYY")

'Update the field impdate in the tbl_temp_Import with the file date

docmd.runsql ("UPDATE tbl_temp_Import SET tbl_temp_Import.impdate = " & filedate & ");")

'You will need to do you update process for the file before progressing to next file


' Deletes old records
DoCmd.RunSQL ("DELETE impActual.* FROM impActual;")
End If

MyName = Dir ' Get next entry.

Loop


Exit_proc:
DoCmd.Hourglass False
DoCmd.Echo True
DoCmd.SetWarnings True
Exit Sub
Err_proc:
MsgBox Err.Description
Resume Exit_proc

End Sub



Hope this helps

Cheers
Darren