vba 用于遍历指定文件夹中的所有 excel 文件并从特定单元格中提取数据的代码

Code for looping through all excel files in a specified folder, and pulling data from specific cells

提问by Justin

I have about 50 or so Excel workbooks that I need to pull data from. I need to take data from specific cells, specific worksheets and compile into one dataset (preferably into another excel workbook).

我有大约 50 个左右的 Excel 工作簿需要从中提取数据。我需要从特定单元格、特定工作表中获取数据并编译成一个数据集（最好是另一个 Excel 工作簿）。

I am looking for some VBA so that I can compile the results into the workbook I am using to run the code.

我正在寻找一些 VBA，以便我可以将结果编译到我用来运行代码的工作簿中。

So, one of the xls or xlsx files I need to pull the data from, worksheet("DataSource"), I need to evaluate cell(D4), and if its not null, then pull data from cell(F4), and put into a new row into the compiled data set. Looping through all the Excel files in that folder as mentioned above.

因此，我需要从中提取数据的 xls 或 xlsx 文件之一，工作表（“数据源”），我需要评估单元格（D4），如果它不为空，则从单元格（F4）中提取数据，然后放入进入编译数据集中的新行。如上所述循环遍历该文件夹中的所有 Excel 文件。

And if possible, I would like the first data field in the first column the name of the file the data is being pulled from in the resulting dataset.

如果可能，我希望第一列中的第一个数据字段是从结果数据集中提取数据的文件的名称。

Can someone help me with this? I am looking for VBA because I am more familiar with that, but also interested in VBScript (as I am trying to get into that and learn the differences).

有人可以帮我弄这个吗？我正在寻找 VBA，因为我对此更熟悉，但也对 VBScript 感兴趣（因为我正试图深入了解其中的差异）。

回答by Phil.Wheeler

First start with this google queryand click the first link that comes up, which takes you to an articleshowing how to iterate through a group of Excel files in a folder.

首先从这个 google 查询开始，然后单击出现的第一个链接，它会将您带到一篇文章，该文章展示了如何遍历文件夹中的一组 Excel 文件。

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = "C:\MyDocuments\TestResults"
            .FileType = msoFileTypeExcelWorkbooks
            'Optional filter with wildcard
            '.Filename = "Book*.xls"
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

                        'DO YOUR CODE HERE

                        wbResults.Close SaveChanges:=False
                    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

To get the name of the workbook, you'll want to adapt the code at "DO YOUR CODE HERE" to include wbResults.Name. If it's the filename you want, use wbResults.FullName, which returns the name of the workbook including its path on disk as a string.

要获得工作簿的名称，您需要修改“在此处执行您的代码”中的代码以包含wbResults.Name. 如果它是您想要的文件名，请使用wbResults.FullName，它返回工作簿的名称，包括其在磁盘上的路径作为字符串。

A search for a VBScript variation on the same thingyields a number of results that are useful, including this script:

在同一事物上搜索VBScript 变体会产生许多有用的结果，包括以下脚本：

strPath = "C:\PATH_TO_YOUR_FOLDER"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder (strPath)

For Each objFile In objFolder.Files

If objFso.GetExtensionName (objFile.Path) = "xls" Then
   Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
   ' Include your code to work with the Excel object here
   objWorkbook.Close True 'Save changes
End If

Next

objExcel.Quit

回答by Cheeso

I would do it in VBScript or even, VB.NET or Powershell if you feel so inclined.

如果您愿意，我会在 VBScript 甚至 VB.NET 或 Powershell 中进行。

Using VB.NET, you can access Excel spreadsheets as if they were databases, via the OLEDB provider. The code to select a range of values might look like this :

使用 VB.NET，您可以通过 OLEDB 提供程序访问 Excel 电子表格，就像访问数据库一样。选择一系列值的代码可能如下所示：

 Try
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        MyConnection = New System.Data.OleDb.OleDbConnection _
        ("provider=Microsoft.Jet.OLEDB.4.0;"  _
        " Data Source='testfile.xls'; " _
         "Extended Properties=Excel 8.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter _
            ("select * from [Sheet1$]", MyConnection)
        MyCommand.TableMappings.Add("Table", "TestTable")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        MyConnection.Close()
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

Once you get the data you can elaborate on it, then insert the result into another Excel spreadsheet, using the same API.

获得数据后，您可以对其进行详细说明，然后使用相同的 API 将结果插入到另一个 Excel 电子表格中。

Getting the list of files is easy in .NET with a call to System.IO.Directory.GetFiles(); just specify the "*.xls" wildcard. Once you have the list, just use a for loop to iterate through it, opening each file in turn, then doing the query on thatfile, and so on.

在 .NET 中获取文件列表很容易，只需调用System.IO.Directory.GetFiles(); 只需指定“*.xls”通配符。获得列表后，只需使用 for 循环遍历它，依次打开每个文件，然后对该文件进行查询，依此类推。

If you use VBScript, then the preferred way to get the list of Excel files is to use the Scripting.FileSystemObject, specifically the GetFolder method. It works basically the same way but the syntax is slightly different.

如果您使用 VBScript，则获取 Excel 文件列表的首选方法是使用Scripting.FileSystemObject，特别是GetFolder 方法。它的工作方式基本相同，但语法略有不同。

---

---

If it's VBScript or VB.NET it will probably run outside of Excel itself. You'd run it by double-clicking or from a batch file or something like that. The advantage to using VB.NET is you could put up a graphical form for interaction - it could show a progress bar, tracking how many files you've gone through, status updates, that kind of thing.

如果它是 VBScript 或 VB.NET，它可能会在 Excel 本身之外运行。您可以通过双击或从批处理文件或类似的文件来运行它。使用 VB.NET 的好处是你可以建立一个用于交互的图形表单——它可以显示一个进度条，跟踪你浏览了多少文件，状态更新，诸如此类。

回答by Nilpo

Whenever you are accessing that many Excel files in succession, you can generally get better performance using ADODB rather than Excel's automation object.

每当您连续访问那么多 Excel 文件时，通常使用 ADODB 而不是 Excel 的自动化对象可以获得更好的性能。

回答by Christopher J. Scharer

I agree with using that accessing the Excel object is not the quickest and if the workbooks and sheets that you're trying to retrieve data from are all consistent (i.e have the same column names, etc... or at least the column names you're looking for) it would be better to use ODBC. This does have some issues and if you can't get around them or need to actually do something more complex based on the contents then there may be no way around it. If that's the case then I would suggest creating one Excel object and then opening and closing the files as needed to try to increase the efficiency.

我同意使用访问 Excel 对象不是最快的方法，并且如果您尝试从中检索数据的工作簿和工作表都是一致的（即具有相同的列名等...或者至少是您的列名） '正在寻找）最好使用 ODBC。这确实有一些问题，如果您无法解决这些问题，或者需要根据内容实际做一些更复杂的事情，那么可能就没有办法解决了。如果是这种情况，那么我建议创建一个 Excel 对象，然后根据需要打开和关闭文件以尝试提高效率。

回答by Rakesh kumar

It could be done with the following code

可以使用以下代码完成

Sub LoopThroughFiles()

Dim StrFile As String
StrFile = Dir("V:\XX\XXX\*.xlsx")
 Do While Len(StrFile) > 0
    Debug.Print StrFile
       Set wbResults = Workbooks.Open("V:\XX\XXX\" & StrFile)   

                    'DO YOUR CODE HERE


       wbResults.Close SaveChanges:=True
    StrFile = Dir
 Loop
End Sub