将第二个变量迭代链接到第一个变量迭代

Question

嗨，我需要一些帮助来完成我放置在 Access 中的以下代码。 我正在寻找有关修改有关 mfile 变量的代码的建议，以便与文件变量在遍历目录中所有 Excel 文件的方式相匹配。

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

Answer 1

我想你只是想在你的循环中移动那条线？

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