Populate field with part of filename on import

[djredden73]

Below is my current code.
Looking to populate a field named Filename_ext with a portion of the filename upon importing.

Filenames are:
AMEX_mmddyyy.txt
NASDAQ_mmddyyyy.txt
NYSE_mmddyyyy.txt
OTCBB_mmddyyyy.txt

Upon import, I would hope to populate Filename_ext with AMEX, NASDAQ, NYSE, OTCBB appropriately.

Sub ImportTextFiles()
    Dim strPath As String
    Dim strFile As String
    Dim strTable As String
    Dim strSpecification As String
    Dim intImportType As AcTextTransferType
    Dim blnHasFieldNames As Boolean
    ' Modify these values as needed
    strTable = "Stock"
    strSpecification = "Import_Spec"
    blnHasFieldNames = False
    intImportType = acImportDelim
    ' Let user select a folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then
            strPath = .SelectedItems(1)
        Else
            MsgBox "You didn't select a folder", vbExclamation
            Exit Sub
        End If
    End With
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    ' Loop through the text files
    strFile = Dir(strPath & "*.txt")
    Do While strFile <> ""
        ' Import text file
        DoCmd.TransferText _
            TransferType:=intImportType, _
            SpecificationName:=strSpecification, _
            TableName:=strTable, _
            FileName:=strPath & strFile, _
            HasFieldNames:=blnHasFieldNames
        strFile = Dir
    Loop
End Sub

 

[pbaldy]

You should be able to use the Left and InStr functions to get the name, then use an update query to populated the field after the import.