Need to pull data from all files in a directory

Question

Possible Duplicate:
Excel - VBA Question. Need to access data from all excel files in a directory without opening the files

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.

I don't have any knowledge of those programs so I'm stuck doing it in Excel VBA. And this is for work so I need to finish it as quickly as possible. — user960358, Sep 23 '11 at 16:00

score 1 · Answer 1 · edited May 23 '17 at 10:26

1

You CAN do this in VBA. And, in this case, you arguably SHOULD.
750 (or even 1000) is NOT excessive. Stop worrying about false economies, per your previous post.
Your basic alogorithm is:

1) Identify the .xls(x) file(s) you need to read 2) Extract the information you need in a loop
There are many different ways to accomplish both 1) and 2).

For example:


    Dim wbList() As String, wbCount As Integer, i As Integer
        FolderName = "C:\Foldername"
        ' create list of workbooks in foldername
        wbName = Dir(FolderName & "\" & "*.xls")
        While wbName  ""
           -- get info, per any of the links you've already been given --
        Wend

edited May 23 '17 at 10:26

Community

1
1

answered Sep 23 '11 at 17:09

paulsm4

114,292
17
138
190

I want to show you the code I tried but it won't fit properly in this section, is there somewhere else I can put it or should I start another question? Sorry, I'm new to all of this... – user960358 Sep 23 '11 at 18:13
I think you should put your code in your initial question that was in progress - you now have three questions open for one problem. Plus more detail is still needed, can the summary workbook contain values, or do you want links back to the source workbooks? – brettdj Sep 23 '11 at 23:26

score 1 · Answer 2 · answered Sep 24 '11 at 02:32

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)

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

score 0 · Answer 3 · answered Sep 23 '11 at 16:29

You can do a file search like this:

' Find all .xls files in a directory
Dim ff As FoundFiles
With Application.FileSearch
    .LookIn = "C:\MyPath\"
    .FileType = msoFileTypeExcelWorkbooks
    .Execute
    Set ff = .FoundFiles
End With
' ff is now a collection of full paths to all excel files in dir
' But you need the filename separate from the folder path...

' FileSystemObject is a convenient tool to manipulate filenames (and more...)
' Before using it, you need to set a reference to it:
' Tools > References > set tickmark next to Microsoft Scripting Runtime
Dim FSO As Scripting.FileSystemObject
Set FSO = New FileSystemObject

Dim i As Integer
For i = 1 To ff.Count
    xlsFilename = FSO.GetFileName(ff.Item(i))
    ' Do your stuff here...

Next i

As an alternative to Application.FileSearch, you can try using the Dir function. Have a look at VBA help for Dir.

I'm not really familiar with the a lot of the functions that you used, I think I'll spend some time reading about this over the weekend. Thanks for the help! — user960358, Sep 24 '11 at 06:33
FileSearch was removed in xl2007, so `Dir` is the standard approach for finding files. — brettdj, Sep 24 '11 at 07:52
Good point. I usually just assume everyone else uses the same antiquated software as I do! — Jean-François Corbett, Sep 24 '11 at 12:07

Need to pull data from all files in a directory

3 Answers3

Linked