需要从目录中的所有文件中提取数据 [英] Need to pull data from all files in a directory

问题描述

可能重复:
Excel-VBA问题.需要访问目录中所有Excel文件中的数据而无需打开文件

因此，我需要从目录中的多个文件中提取数据并将其粘贴到一个excel文件中，而无需打开文件.有人很乐于为我提供有关如何对单个文件执行此操作的代码，现在我只需要弄清楚如何对目录中的所有文件执行此操作.这段代码是针对单个单元格的，我需要一定范围的代码.那不是问题，但我只是想也提到它.

So I need to pull data from multiple files in a directory and paste it into one excel file without having to open the files. Someone was nice enough to provide me with code on how to do that for a single file, now I just need to figure out how to do this for all the files in the directory. This code is for a single cell and I need it for a range. That's not an issue but I just thought I'd mention it as well.

Dim rngDestinationCell As Range
Dim rngSourceCell As Range
Dim xlsPath As String
Dim xlsFilename As String
Dim sourceSheetName As String

Set rngDestinationCell = Cells(3,1) ' or Range("A3")
Set rngSourceCell = Cells(3,1)
xlsPath = "C:\MyPath"
xlsFilename = "MyBook.xls"
sourceSheetName = "Sheet1"

rngDestinationCell.Formula = "=" _
& "'" & xlsPath & "\[" & xlsFilename & "]" & sourceSheetName & "'!" _
& rngSourceCell.Address

所以我假设我必须做某种循环才能遍历所有文件，但是我不确定该怎么做.如果有人可以帮助我，我将非常感激.

So I'm assuming I have to do some sort of loop to run through all the files but I'm not sure how to do it. If someone can help me with this I'd really really appreciate it.

推荐答案

虽然我认为这篇文章应该已经在您的第一个线程中完成了，但是您可以使用下面的代码，该代码来自于我提供的用于合并第2行的较早链接.您可以指定的文件夹中的每个称为日志表单"的工作表的名称(将C:\ temp更改为您的路径)

While I think this post should have been finished in your first thread, you can use the code below which is derived from the earlier link I provided to consolidate row 2 of each sheet called "loging form" from a folder you can specify (change C:\temp to your path)

代码将查看 .xls ，因此它将在Excel 2003文件或Excel 2007/10文件上运行.Dir适用于所有版本.该代码会跳过任何不包含名为日志记录表"的工作表的工作簿.

The code looks at .xls so it will work on Excel 2003 files, or Excel 2007/10 files. Dir works on all versions. The code skips any workbooks that don't contain a sheet called "loging form"

最后，返回的行将合并到承载代码的工作簿的新工作表(作为值)上，每次运行代码时都会创建一个新工作表

Lastly the returned rows are consolidated on a new sheet (as values) of the workbook that hosts the code, a new sheet is created each time the code runs

Sub ConFiles()

    Dim Wbname As String
    Dim Wb As Workbook
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim lngCalc As Long
    Dim lngrow As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        lngCalc = .CalculationState
        .Calculation = xlCalculationManual
    End With


   Set ws1 = ThisWorkbook.Sheets.Add
    'change folder path here
    FolderName = "C:\temp"
    Wbname = Dir(FolderName & "\" & "*.xls*")

    'ThisWorkbook.Sheets(1).UsedRange.ClearContents
    Do While Len(Wbname) > 0
        Set Wb = Workbooks.Open(FolderName & "\" & Wbname)
        Set ws = Nothing
        On Error Resume Next
        'change sheet name here
        Set ws = Wb.Sheets("loging form")
        On Error GoTo 0
        If Not ws Is Nothing Then
        lngrow = lngrow + 1
        ws.Rows(2).Copy ws1.Cells(lngrow, "A")
        End If
        Wb.Close False
        Wbname = Dir
    Loop

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = lngCalc
    End With
End Sub

这篇关于需要从目录中的所有文件中提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！