循环遍历指定文件夹中的所有 Excel 文件并从特定单元格中提取数据的代码

Question

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

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

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

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

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

Answer 1

首先从此 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

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

搜索 同一事物的 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

Answer 2

如果您愿意的话，我会用 VBScript 甚至 VB.NET 或 Powershell 来完成。

使用 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

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

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

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

---

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

Answer 3

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

Answer 4

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

Answer 5

可以用下面的代码来完成

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