Need to pull data from all files in a directory [d

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.

Answer 1

1. You CAN do this in VBA. And, in this case, you arguably SHOULD.

2. 750 (or even 1000) is NOT excessive. Stop worrying about false economies, per your previous post.

3. Your basic alogorithm is:

   1) Identify the .xls(x) file(s) you need to read 2) Extract the information you need in a loop

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

Answer 2

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

Answer 3

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.