Access add filename while importing multiple TXT to last column using VBA

Viewed 85

I currently have a macro that imports all files within a folder using an Access Specification. I'd like to add a column at the end of the Access table with the filename of each imported .txt file. This is my code so far. How do I get it done?

Sub cmdImport_Click()
    Dim strPath As String
    Dim strFile As String
    Dim strTable As String
    Dim strSpecification As String
    Dim intImportType As AcTextTransferType
    Dim blnHasFieldNames As Boolean

    strTable = "Reconstrucción"
    strSpecification = "Reconstruir"
    blnHasFieldNames = False
    intImportType = acImportDelim

    ' Permite al usuario elegir una carpeta
    With Application.FileDialog(4)
        If .Show Then
            strPath = .SelectedItems(1)
        Else
            MsgBox "No seleccionó una carpeta", vbExclamation
            Exit Sub
        End If
    End With
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If

    DoCmd.OpenForm "frmMessage"
    Forms!frmMessage.Repaint

    ' Loop a través de los archivos de texto
    strFile = Dir(strPath & "*.txt")
    Do While strFile <> ""
        ' Importa los archivos de texto de la carpeta
        DoCmd.TransferText _
            TransferType:=acImportFixed, _
            SpecificationName:=strSpecification, _
            TableName:=strTable, _
            FileName:=strPath & strFile, _
            HasFieldNames:=blnHasFieldNames
        strFile = Dir
    Loop

    DoCmd.Close acForm, "frmMessage"
End Sub
1 Answers

Just create a field called FileName in the table "Reconstrucción" and add the code belw as the last row of the loop:

docmd.runsql "UPDATE " & strTable & " SET FileName = '" & strFile & "' WHERE strFile ISNULL;"
Related