用于循环遍历指定文件夹中的所有 excel 文件并从特定单元格中提取数据的代码 [英] Code for looping through all excel files in a specified folder, and pulling data from specific cells

问题描述

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

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).

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

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

因此，我需要从中提取数据的 xls 或 xlsx 文件之一，工作表(数据源")，我需要评估单元格(D4)，如果它不为空，则从单元格中提取数据(F4), 并放入新行到编译数据集中.如上所述循环遍历该文件夹中的所有 Excel 文件.

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.

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

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.

有人可以帮我解决这个问题吗?我正在寻找 VBA，因为我更熟悉它，但也对 VBScript 感兴趣(因为我正在努力研究它并了解差异).

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).

推荐答案

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

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

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:MyDocumentsTestResults"
            .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，它返回工作簿的名称，包括其在磁盘上的路径作为字符串.

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.

搜索 同一事物的VBScript变体有用的结果数量，包括此脚本:

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

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

这篇关于用于循环遍历指定文件夹中的所有 excel 文件并从特定单元格中提取数据的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！