打开/运行宏/保存并关闭

Question

我想一旦上的文件夹中的所有文件自动宏的执行，使用此代码：

Sub LoopFile() 
Dim fso as Object 'Scritping.FileSystemObject 
Dim fldr as Object 'Scripting.Folder 
Dim file as Object 'Scripting.File 
Dim wb as Workbook 

Set fso = CreateObject("Scripting.FileSystemObject") 
Set fldr = fso.GetFolder("C:\DataBanks001\TEST\") 

For Each file In fldr.Files 
    Set wb = Workbooks.Open(file.Path) 
    Call ListWorkSheetAndUpdate 

    wb.Close (True) 
Next 

Set file = Nothing 
Set fldr = Nothing 
Set fso = Nothing 
End Sub 

的问题是，一旦所有文件已经处理它循环回到开始并重新开始 - 基本上在每个工作簿上不止一次地运行同一个宏;东拼西凑的解决方案是使用其他代码：

Sub AllFiles() 
Dim folderPath As String 
Dim filename As String 
Dim wb As Workbook 

folderPath = "C:\DataBanks001\TEST\" 

If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\" 

filename = Dir(folderPath & "*.xls") 
Do While filename <> "" 
    Application.ScreenUpdating = False 
    Set wb = Workbooks.Open(folderPath & filename) 

    Call ListWorkSheetAndUpdate 

    filename = Dir 
Loop 
Application.ScreenUpdating = True 
End Sub 

不收&保存，但处理工作簿中只有一次 - 但让他们开了会导致Excel崩溃它们的累积规模变得太大了。
有没有办法在文件夹中的最后一个文件被处理后停止代码？

由于我的宏在工作簿中创建了一个新的汇总表，我在考虑可能的解决方法是计算每个打开的工作簿中的工作表，并且如果该数字大于（n + 1）则停止子工作。这可能是一个可能的解决方案？

@findwindow - 代码如下少一些公式：

Sub ListWorkSheetAndUpdate() 
Dim xWs As Worksheet 
On Error Resume Next 
Application.DisplayAlerts = False 

Sheets.Add.Name = "Master" 

xTitleId = "KutoolsforExcel" 
Application.Sheets(xTitleId).Delete 
Application.Sheets.Add Application.Sheets(1) 
Set xWs = Application.ActiveSheet 
xWs.Name = xTitleId 
For I = 2 To Application.Sheets.Count 
    xWs.Range("A" & (I - 1)) = Application.Sheets(I).Name 
Next 
Application.DisplayAlerts = True 

Worksheets("Master").Range("A1").Value = "Sheet" 
Worksheets("Master").Range("B1").Value = "SAE" 
Worksheets("Master").Range("C1").Value = "CODE" 
Worksheets("Master").Range("D1").Value = "COD_TRX" 
Worksheets("Master").Range("E1").Value = "Add Index" 
Worksheets("Master").Range("F1").Value = "Add Match" 
Worksheets("Master").Range("G1").Value = "Add SIC" 
Worksheets("Master").Range("H1").Value = "GRID" 
Worksheets("Master").Range("J1").Value = "VART" 
Worksheets("Master").Range("K1").Value = "OP.R" 

Worksheets("Master").Range("A2:A101").Formula = "=KutoolsforExcel!RC" 
Worksheets("Master").Range("B2:B101").Formula = "=INDIRECT(CONCATENATE(RC[-1],""!A1""))" 
Worksheets("Master").Range("C2:C101").Formula = "=RIGHT(LEFT(RC[9],LEN(RC[9])-14),5)" 
Worksheets("Master").Range("D2:D101").Formula = "=RIGHT(LEFT(LEFT(RC[8],LEN(RC[8])-14),LEN(LEFT(RC[8],LEN(RC[8])-14))-5),LEN(LEFT(LEFT(RC[8],LEN(RC[8])-14),LEN(LEFT(RC[8],LEN(RC[8])-14))-5))-(FIND(""~"",SUBSTITUTE(LEFT(LEFT(RC[8],LEN(RC[8])-14),LEN(LEFT(RC[8],LEN(RC[8])-14))-5),"","",""~"",(LEN(LEFT(LEFT(RC[8],LEN(RC[8])-14),LEN(LEFT(RC[8],LEN(RC[8])-14))-5))-LEN(SUBSTITUTE(LEFT(LEFT(RC[8],LEN(RC[8])-14),LEN(LEFT(RC[8],LEN(RC[8])-14))-5),"","",""""))))))-1)" 
Worksheets("Master").Range("E2:E101").Value = "!A:D" 
Worksheets("Master").Range("F2:F101").Value = "!C:C" 
Worksheets("Master").Range("G2:G101").Value = "!A:A" 
Worksheets("Master").Range("H2:H101").Formula = "=INDEX(INDIRECT(CONCATENATE(RC[-7],RC[-3])),MATCH(R1C8,INDIRECT(CONCATENATE(RC[-7],RC[-2])),0),4)" 
Worksheets("Master").Range("J2:J101").Formula = "=INDEX(INDIRECT(CONCATENATE(RC[-9],RC[-5])),MATCH(R1C10,INDIRECT(CONCATENATE(RC[-9],RC[-3])),0),2)" 
Worksheets("Master").Range("K2:K101").Formula = "=INDEX(INDIRECT(CONCATENATE(RC[-10],RC[-6])),MATCH(R1C11,INDIRECT(CONCATENATE(RC[-10],RC[-4])),0),2)" 


Worksheets("Master").Columns("A:A").EntireColumn.AutoFit 
Worksheets("Master").Columns("B:B").EntireColumn.AutoFit 
Worksheets("Master").Columns("C:C").EntireColumn.AutoFit 
Worksheets("Master").Columns("D:D").EntireColumn.AutoFit 
Worksheets("Master").Columns("E:E").EntireColumn.AutoFit 
Worksheets("Master").Columns("F:F").EntireColumn.AutoFit 
Worksheets("Master").Columns("G:G").EntireColumn.AutoFit 
Worksheets("Master").Columns("H:H").EntireColumn.AutoFit 
Worksheets("Master").Columns("I:I").EntireColumn.AutoFit 
Worksheets("Master").Columns("J:J").EntireColumn.AutoFit 
Worksheets("Master").Columns("K:K").EntireColumn.AutoFit 
Worksheets("Master").Columns("L:L").EntireColumn.AutoFit 

Sheets("KutoolsforExcel").Visible = False 

End Sub 

Answer 1

每当我针对的情形，我通过文件的目录循环的情况下，我创建了一个单独的工作表的日志文件已经迭代过的路径。然后，您可以编写脚本来仅处理尚未处于索引中的文件。这是额外的处理，但一个简单的解决方案。一旦所有文件出现在日志中，就可以结束执行。