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

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

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