Excel VBA：如何循环使用给定代码的相同文件夹中的工作簿？ [英] Excel VBA: How to Loop Through Workbooks in Same Folder using Given Code?

问题描述

（

而且，这是一个例如Test01.xlsx的样子：

要注意，有10个测试文件（Test01，Test02 ...），但代码应该能够更新任何新的测试文件添加（例如。 Test11，Test12 ...）。我有一个想法，将第一张图片中的文件列整合到文件名中并循环。

解决方案

最简单的方法是使用 filesystemobject 循环遍历文件夹中的所有文件，并找到文件名类似于前导掩码的掩码（在您的情况下）测试* .xslx）。请注意，它也会通过指定文件夹中的子文件夹。如果不需要，则为每个循环省略第一个循环：

  Dim fso As Object'FileSystemObject 
 Dim fldStart As对象'文件夹
 Dim fld As Object'Folder 
 Dim fl As Object'File 
 Dim oWBWithColumn As Workbook 
 Dim oWbMaster as workbook 
 Dim oWsSource as workheet 
 Dim oWsTarget as工作表
 Dim Mask As String 
 Dim k as long 
k = 2 
设置oWbMaster = ActiveWorkbook 
设置oWsTarget = oWbMaster.Sheets（Sheet1） 
设置fso = CreateObject（scripting.FileSystemObject）
 
设置fldStart = fso.GetFolder（C：\Users\khanr1\Desktop\CodeUpdateTest\）
 
 Mask =Test *& .xlsx
对于每个fld在fldStart.Subfolders 
 
对于每个fl在fld.Files 
 
如果fl.Name Like Mask Then 
 
设置oWBWithColumn = Application.Workbooks.Open（文件名：= fld.Path&\& fl.Name，ReadOnly：= True）
设置oWsSource = oWBWithColumn.Worksheets（Sheet2） 
 
 oWsTarget.Range（B& k）.Value = Application.WorksheetFunction.CountIf（oWsSource.Range（B：B），YES）
 
 oWBWithColumn.Close SaveChanges：= False 
k = k + 1 
 
如果
 
下一个
 
下一个
  

如果此答案有帮助，请标记为已接受。还要注意，您的原始代码将在循环的每次迭代中替换主电子表格中的B2单元格的值，这就是为什么我添加了 k 变量来更改目标单元格每个迭代

PS

您可以生成文件列表以及文件夹中的yes计数，同时，只需在关闭文件之前将代码添加到代码中：

  oWsTarget.Range（A& k ）.Value = fl.Name 
  

(Previous Post)

I need to create a macro that loops through the files that are in a single folder and runs the code that I have provided below. All the files are structured the same way however, have different data. The code helps me go to a specified destination file and counts the number of "YES" in the column. Then it outputs it into a CountResults.xlsm (master workbook). I have the following code with the help of Zac:

Private Sub CommandButton1_Click()

    Dim oWBWithColumn As Workbook: Set oWBWithColumn = Application.Workbooks.Open("C:\Users\khanr1\Desktop\CodeUpdateTest\Test01.xlsx")
    Dim oWS As Worksheet: Set oWS = oWBWithColumn.Worksheets("Sheet2")

    ThisWorkbook.Worksheets("Sheet1").Range("B2").Value = Application.WorksheetFunction.CountIf(oWS.Range("B:B"), "YES")

    oWBWithColumn.Close False

    Set oWS = Nothing
    Set oWBWithColumn = Nothing

End Sub

This is what the CountResults.xlsm (Master Workbook) looks like:

And, this is an example of what the Test01.xlsx looks like:

To note, there are 10 test files (Test01, Test02...) but the code should be able to update any new test files added (ex. Test11, Test12...). I had an idea of incorporating the "Files" column in the first image to pull the file names and loop them.

解决方案

The easiest way to do so would be to use the filesystemobject to loop through all the files in the folder and find the ones where filename is similar to the predetrmined mask( in your case "Test*.xslx"). Please note that it also goes through subfolders in the specified folder. If that is not required, omit the first for each loop:

Dim fso As Object 'FileSystemObject
Dim fldStart As Object 'Folder
Dim fld As Object 'Folder
Dim fl As Object 'File
Dim oWBWithColumn As Workbook
Dim oWbMaster as workbook
Dim oWsSource as worksheet
Dim oWsTarget as worksheet
Dim Mask As String
Dim k as long
k=2
Set oWbMaster = ActiveWorkbook
Set oWsTarget = oWbMaster.Sheets("Sheet1")
Set fso = CreateObject("scripting.FileSystemObject")

Set fldStart = fso.GetFolder("C:\Users\khanr1\Desktop\CodeUpdateTest\")

Mask = "Test*" & ".xlsx"
For Each fld In fldStart.Subfolders

    For Each fl In fld.Files

    If fl.Name Like Mask Then

    Set oWBWithColumn = Application.Workbooks.Open(Filename:=fld.Path & "\" & fl.Name, ReadOnly:=True)
    Set oWsSource = oWBWithColumn.Worksheets("Sheet2")

        oWsTarget.Range("B"& k).Value = Application.WorksheetFunction.CountIf(oWsSource.Range("B:B"), "YES")

        oWBWithColumn.Close SaveChanges:=False
        k = k+1

    End If

Next

Next

If this answer helps, please mark as accepted. Also note that your original code would replace the value of B2 cell in the master spreadsheet every iteration of the loop, that's why I have added the k variable to change the target cell after each iteration

P.S.

You can generate a list of files along with the yes counts from the folder all at the same time, just add this line to the code before closing the file:

oWsTarget.Range("A"& k).Value= fl.Name

这篇关于Excel VBA：如何循环使用给定代码的相同文件夹中的工作簿？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！