Results 1 to 8 of 8
  1. #1
    Join Date
    May 2014
    Posts
    4

    Unanswered: Need assistance modifying this RecursiveDir script

    I've given up and have come to the experts for help. Any assistance will be greatly appreciated. So here is what I need..I found a VBA script that allows me to loop through a folder and sub folders within that folder to import all the files within those. The problem I have is that within each sub folder are 3 files that i need to have to to separate tables. Now, the way it is set up, everything gets imported into one table.

    Is there any way to get this done? or am i just wasting time..

    oh and one more thing i forgot to mention is that the file names in each folder dont change except for one, which has a constant first part but the second part changes in every folder.

    Code:
    Private Sub btnBrowseForFolder_Click()
    
    	Const strcTableName As String = "Cur Mo SD Records" 'table
    	Const strcSpecName As String = "Cur Mo Svc Detail" 'import spec
    	Const strcTableName2 As String = "Cur Mo Bill Summary" 'table
    	Const strcSpecName2 As String = "BillSum" 'import spec 
    	Dim colFiles As New Collection, intFile As Integer
        	Dim strPath As String
    
        strPath = BrowseFolder("Choose Folder For Import")
    
    
        RecursiveDir colFiles, strPath, "*.txt", True
    
     '  RecursiveDir colFiles, strPath, “BillSummary*.txt", True
     
       		
        If Len(strPath) > 0 Then
    
    	Dim vFile As Variant
    
        For Each vFile In colFiles
      
      '  If vFile Like "Bill*.*" Then
            Debug.Print vFile
            intFile = intFile + 1
         '   If vFile = “ServiceDetail.txt" Then
    
        End If
            DoCmd.TransferText acImportDelim, _
            strcSpecName, strcTableName, vFile _
        
    Next vFile
    
         MsgBox intFile & " Files were Imported"
    
    
    End Sub


    and here is the second part which i did not modify

    Code:
    
    Option Compare Database
    
    Public Function RecursiveDir(colFiles As Collection, _
                                 strFolder As String, _
                                 strFileSpec As String, _
                                 bIncludeSubfolders As Boolean)
    
    '   This function was acquired from:
    '   http://www.ammara.com/access_image_faq/recursive_folder_search.html
    
        Dim strTemp As String
        Dim colFolders As New Collection
        Dim vFolderName As Variant
    
        'Add files in strFolder matching strFileSpec to colFiles
        strFolder = TrailingSlash(strFolder)
        strTemp = Dir(strFolder & strFileSpec)
        Do While strTemp <> vbNullString
            colFiles.Add strFolder & strTemp
            strTemp = Dir
        Loop
    
        If bIncludeSubfolders Then
            'Fill colFolders with list of subdirectories of strFolder
            strTemp = Dir(strFolder, vbDirectory)
            Do While strTemp <> vbNullString
                If (strTemp <> ".") And (strTemp <> "..") Then
                    If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                        colFolders.Add strTemp
                    End If
                End If
                strTemp = Dir
            Loop
    
            'Call RecursiveDir for each subfolder in colFolders
            For Each vFolderName In colFolders
                Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
            Next vFolderName
        End If
    
    End Function
    
    
    Public Function TrailingSlash(strFolder As String) As String
        If Len(strFolder) > 0 Then
            If Right(strFolder, 1) = "\" Then
                TrailingSlash = strFolder
            Else
                TrailingSlash = strFolder & "\"
            End If
        End If
    End Function
    Thank you in advance

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. What are the names or constant part of names of the 3 files?

    2. What are the names of the matching tables?

    3. What are the names of the import spec. for each file?
    Code:
    FileName1 --> Table1 --> ImportSpec1
    FileName2 --> Table2 --> ImportSpec2
    FileName3 --> Table3 --> ImportSpec3
    Have a nice day!

  3. #3
    Join Date
    May 2014
    Posts
    4
    Quote Originally Posted by Sinndho View Post
    1. What are the names or constant part of names of the 3 files?

    2. What are the names of the matching tables?

    3. What are the names of the import spec. for each file?
    Code:
    FileName1 --> Table1 --> ImportSpec1
    FileName2 --> Table2 --> ImportSpec2
    FileName3 --> Table3 --> ImportSpec3

    Hey, thanks for the reply. The three files are named as follows

    -BillSummary(followed by variable date and account info).txt
    -FGC_Serv_Records.txt
    -FGC_Debits_Credits.txt

    Tables are

    Cur Mo Bill Sum
    Cur Mo Serv Rec
    Cur Mo Adj

    And import specs are

    BillSumImport
    MonthlySvImport
    AdjustmentImport


    Feel free to ask me any other questions

    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution:
    Code:
    Public Sub btnBrowseForFolder_Click()
    
        Const strcTableName As String = "Cur Mo Bill Sum;Cur Mo Serv Rec;Cur Mo Adj"    'tables
        Const strcSpecName As String = "BillSumImport;MonthlySvImport;AdjustmentImport" 'import spec
        
        Dim colFiles As New Collection
        Dim intFile As Integer
        Dim strPath As String
        Dim vFile As Variant
        Dim varTables As Variant
        Dim varSpecs As Variant
        Dim lngindex As Long
    
        varTables = Split(strcTableName, ";")
        varSpecs = Split(strcSpecName, ";")
    '
    ' -->  Index | varTables       | varSpecs
    '      ------+-----------------+----------------
    '         0  | Cur Mo Bill Sum | BillSumImport
    '      ------+-----------------+----------------
    '         1  | Cur Mo Serv Rec | MonthlySvImport
    '      ------+-----------------+----------------
    '         2  | Cur Mo Adj      | AdjustmentImport
    '
        strPath = browsefolder("Choose Folder For Import")
        If Len(strPath) > 0 Then
            RecursiveDir colFiles, strPath, "*.txt", True
            For Each vFile In colFiles
                If vFile Like "*\BillSummary*" Then
                    lngindex = 0
                ElseIf vFile Like "*\FGC_Serv_Records.txt" Then
                    lngindex = 1
                ElseIf vFile Like "*\FGC_Debits_Credits.txt" Then
                    lngindex = 2
                Else
                    lngindex = -1
                End If
                Select Case lngindex
                    Case 0 To 2
                        intFile = intFile + 1
                        DoCmd.TransferText acImportDelim, varSpecs(lngindex), varTables(lngindex), vFile
                    Case Else
                        '
                        ' Unexpected file --> handle the case (error message, ...)
                        '
                End Select
            Next vFile
        End If
        MsgBox intFile & " Files were Imported"
    
    End Sub
    Note: You should not use spaces or any other non-alphanumeric in the names of the tables or any other objects in your projects. The same restriction rules for the reserved words: Access 2007 reserved words and symbols - Access
    Have a nice day!

  5. #5
    Join Date
    May 2014
    Posts
    4
    Quote Originally Posted by Sinndho View Post
    Here's a solution:
    Code:
    Public Sub btnBrowseForFolder_Click()
    
        Const strcTableName As String = "Cur Mo Bill Sum;Cur Mo Serv Rec;Cur Mo Adj"    'tables
        Const strcSpecName As String = "BillSumImport;MonthlySvImport;AdjustmentImport" 'import spec
        
        Dim colFiles As New Collection
        Dim intFile As Integer
        Dim strPath As String
        Dim vFile As Variant
        Dim varTables As Variant
        Dim varSpecs As Variant
        Dim lngindex As Long
    
        varTables = Split(strcTableName, ";")
        varSpecs = Split(strcSpecName, ";")
    '
    ' -->  Index | varTables       | varSpecs
    '      ------+-----------------+----------------
    '         0  | Cur Mo Bill Sum | BillSumImport
    '      ------+-----------------+----------------
    '         1  | Cur Mo Serv Rec | MonthlySvImport
    '      ------+-----------------+----------------
    '         2  | Cur Mo Adj      | AdjustmentImport
    '
        strPath = browsefolder("Choose Folder For Import")
        If Len(strPath) > 0 Then
            RecursiveDir colFiles, strPath, "*.txt", True
            For Each vFile In colFiles
                If vFile Like "*\BillSummary*" Then
                    lngindex = 0
                ElseIf vFile Like "*\FGC_Serv_Records.txt" Then
                    lngindex = 1
                ElseIf vFile Like "*\FGC_Debits_Credits.txt" Then
                    lngindex = 2
                Else
                    lngindex = -1
                End If
                Select Case lngindex
                    Case 0 To 2
                        intFile = intFile + 1
                        DoCmd.TransferText acImportDelim, varSpecs(lngindex), varTables(lngindex), vFile
                    Case Else
                        '
                        ' Unexpected file --> handle the case (error message, ...)
                        '
                End Select
            Next vFile
        End If
        MsgBox intFile & " Files were Imported"
    
    End Sub
    Note: You should not use spaces or any other non-alphanumeric in the names of the tables or any other objects in your projects. The same restriction rules for the reserved words: Access 2007 reserved words and symbols - Access


    WOW! this is almost perfect, does exactly what i need thank you so much. is it possible to make one slight modification? one of the files is being imported as a acImportFixed (BillSummary*) file and the others are acImportDelim (FGC_Serv_Records.txt,FGC_Debits_Credits.txt). is there any way to add that to the case statement? Sorry, should have mentioned that earlier.


    Thanks in advance

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sure:
    Code:
                Select Case lngindex
                    Case 0 
                        intFile = intFile + 1
                        DoCmd.TransferText acImportFixed , varSpecs(lngindex), varTables(lngindex), vFile
                    Case 1, 2
                        intFile = intFile + 1
                        DoCmd.TransferText acImportDelim, varSpecs(lngindex), varTables(lngindex), vFile
                    Case Else
                        '
                        ' Unexpected file --> handle the case (error message, ...)
                        '
                End Select
    Have a nice day!

  7. #7
    Join Date
    May 2014
    Posts
    4
    Quote Originally Posted by Sinndho View Post
    Sure:
    Code:
                Select Case lngindex
                    Case 0 
                        intFile = intFile + 1
                        DoCmd.TransferText acImportFixed , varSpecs(lngindex), varTables(lngindex), vFile
                    Case 1, 2
                        intFile = intFile + 1
                        DoCmd.TransferText acImportDelim, varSpecs(lngindex), varTables(lngindex), vFile
                    Case Else
                        '
                        ' Unexpected file --> handle the case (error message, ...)
                        '
                End Select




    You're awesome. You have no idea how much I appreciate this. Thank you!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •