CODEWITHSUNDEEP
vbaloopsms-access
BossRoyce
BossRoyce

Reputation: 211

Linking Second Variable Iteration to First

Hi I need some help to finalize the following code I've placed in Access. I'm looking for suggestions in modifying the code regarding the mfile variable so that matches the file variable in how it iterates through all the Excel files in the directory.

Public Function load_data()
'mfile is modified filename with "xl" prefix to group all imported tables 
together

Dim file, mfile As Variant    
file = Dir(CurrentProject.Path & "\")
mfile = "xl_" & Left(file, Len(file) - 5)

Do Until file = ""    
    Debug.Print mfile           
    If file Like "*.xlsx" Then            
        For Each tbl In CurrentDb.TableDefs            
            If mfile = tbl.Name Then                
                DoCmd.DeleteObject acTable, tbl.Name
                Exit For                
            End If            
        Next tbl           
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, mfile, CurrentProject.Path & "\" & file, True           
    End If           
    file = Dir           
Loop

End Function

Upvotes: 0

Views: 37

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

I think you just want to move that line inside your loop? 

Public Function load_data()

    'mfile is modified filename with "xl" prefix to group 
    '   all imported tables together

    Dim file, mfile As Variant    
    file = Dir(CurrentProject.Path & "\")

    Do Until file = ""

        If file Like "*.xlsx" Then

            mfile = "xl_" & Left(file, Len(file) - 5) '<< move inside loop
            Debug.Print mfile           

            For Each tbl In CurrentDb.TableDefs            
                If mfile = tbl.Name Then                
                    DoCmd.DeleteObject acTable, tbl.Name
                    Exit For                
                End If            
            Next tbl           
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
                            mfile, CurrentProject.Path & "\" & file, True           
        End If   

        file = Dir           
    Loop

End Function

Upvotes: 1

Related Questions