Import Loop for files in MS Access: How to run macros on each file and replace the contents of a sta-CodePudding

I'm working with MS Access to import a large number of text files from a folder. Each text file has the same structure, and a big header which contains useful data. I import each text file as a space delimited file into a staging table, and use a series of queries to collect the relevant info and append everything to a master table. I am trying to automate this process, and have created a macro to run all of the queries. I'm using the following VBA to try and loop through each file in the folder, but I'm running into a couple of problems. I cannot seem to get the next file import to replace the contents of my staging table for each iteration. I am new to VBA so any hints or tips would be appreciated!

Public Sub LoopThroughFiles()
Dim strFileName As String
Dim intNumberOfFiles As Integer

intNumberOfFiles = 0

strFileName = Dir("My_File_Path\*", vbNormal)

Do Until strFileName = ""
    intNumberOfFiles = intNumberOfFiles   1
    strFileName = Dir()
      DoCmd.TransferText acImportDelim, My_Import_Spec, "My_Staging_Table", Dir(), False
  DoCmd.RunMacro ("RunMacros")
  
    
Loop


End Sub

CodePudding user response：

I would think maybe something like this should work:

    Const SRC_PATH As String = "C:\Testing\" 'for example
    
    Dim strFileName As String
    Dim NumberOfFiles As Long 'prefer Long to Integer
    
    NumberOfFiles = 0
    
    strFileName = Dir(SRC_PATH & "*.txt", vbNormal)
    
    Do Until strFileName = ""
        NumberOfFiles = NumberOfFiles   1
        
        DoCmd.TransferText acImportDelim, My_Import_Spec, "My_Staging_Table", _
                           SRC_PATH & strFileName, False
        
        DoCmd.RunMacro "RunMacros"
      
        strFileName = Dir() 'next file
    Loop

    MsgBox NumberOfFiles & " files imported"

CodePudding user response：

Is this what you want?

Private Sub Command0_Click()

        Dim strPathFile As String, strFile As String, strPath As String
        Dim strTable As String
        Dim blnHasFieldNames As Boolean

        blnHasFieldNames = True

        strPath = "C:\Users\ryans\OneDrive\Desktop\test\"

        strTable = "Table1"

        strFile = Dir(strPath & "*.txt")
        Do While Len(strFile) > 0
              strPathFile = strPath & strFile
              DoCmd.TransferText acImportDelim, _
                TableName:="Test1", _
                FileName:=strPath & strFile, _
                HasFieldNames:=True
              strFile = Dir()
        Loop


End Sub

If not, post back with more details, please.