Import multible text files into Access table

[mburch2000]

I am trying to import multiple text files into an Access table. I build a Form with a button, named "Import_Dea ls". In the On click Event trigger I entered the following code, but it don't import my files. can someone please help?

Thanks,
Mike

Private Sub Import_Deals_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
    Dim objFileDialog As Office.FileDialog
    Set objFileDialog = Application.FileDialog(MsoFileDialogType.msoFileDialogFolderPicker)
    ' Modify these values as needed
    strTable = "EERS_Deals"
    strSpecification = "EERS_Deals"
    blnHasFieldNames = False
    intImportType = acImportDelim
    ' Let user select a folder
    With objFileDialog
        .AllowMultiSelect = True
        .ButtonName = "Folder Picker"
        .Title = "Folder Picker"
        If (.SelectedItems.Count > 0) Then
            Call MsgBox(.SelectedItems(1))
        ElseIf .Show > 0 Then
        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

[Seth Schrock]

Do you get an error message running this code or does nothing happen?

[mburch2000]

When I run it from the VBE window, the Macro MsgBox pops up apparently looking for a Macro. I finally realized that my settings under File/Option/Trust Center Settings/Macros Settings needed to default to Enable All Macros. I got something to work before I left, but I will not be able to test anything else for two days when I return. Thanks for your help. The above code still did not work like I wanted though. I really need to select all files in directory, then upload then into an existing table in Access.

[cPmod]

Hi Mike,

As far as I understood you simply want to process all text files in one directory or something like that processed, right?

Following code (it is excel vba, my apologies) did this job for me (I know it is probably a bit dirty coding but it worked like a charm converting a lot of pdf files to txt).

    Dim i As Integer
    strTXT As String
    Dim FSO As Object, objSFold As Object, objWks As Object, tmp As Object
    Dim colPFiles As New Collection, colTFiles As New Collection
    Dim Multi
    Dim strManuf As String
    Dim strFoldername As String



    While colPFiles.Count <> 0
        colPFiles.Remove (colPFiles.Count)
    Wend

    strFoldername = ""
    strManuf = ""
    
    strFoldername = ThisWorkbook.Path & "\" & strManuf
    Set objSFold = FSO.GetFolder(strFoldername)
    Set objWks = ThisWorkbook.Sheets(1)                            
    For Each tmp In objSFold.Files  ' read in all Filenames  
        If Right(tmp.Path, 4) = ".pdf" Then colPFiles.Add tmp.Path  ' only *.pdf
    Next tmp
 
    For i = 1 To colPFiles.Count
    [B]'Perform Task! -> the Files can be individually addressed one after another using 'colPFiles.Item(i)' [/B]
    Next
'MsgBox "Job finished!"
End Sub

As I said, unfortunately I made this code work in excel vba and it therefore might need minor modifications (however, the data manipulation is done outside excel!) but it might guide you to the right path and you may modify that example for your task.

If you have further questions just let me know.

Cheers

[ADezii]

The Code will never work in its current context. See Revised Code below along with a Demo Upload that should point you in the right direction.

Dim strPath As String
Dim strFile As String
Dim strTable As String
Dim strSpecification As String
Dim intImportType As AcTextTransferType
Dim blnHasFieldNames As Boolean
Dim objFileDialog As Office.FileDialog

Set objFileDialog = Application.FileDialog(MsoFileDialogType.msoFileDialogFolderPicker)

'**** Modify these values as needed ****
strTable = "EERS_Deals"
strSpecification = "EERS_Deals"
blnHasFieldNames = False
intImportType = acImportDelim
'***************************************

'Let user select a folder
With objFileDialog
  .AllowMultiSelect = False     'Meaningless, but set anyway
  .ButtonName = "Folder Picker"
  .Title = "Folder Picker"
     If .Show Then
       strPath = .SelectedItems(1) & "\"
       
       'Loop through the text files
       strFile = Dir(strPath & "*.txt")
         Do While strFile <> ""
           'For Testing purposes only
           DoCmd.TransferText intImportType, , strTable, strPath & strFile, False
         strFile = Dir
         Loop
     End If
End With