Query all excel files in a directory

[Greg500]

What is the best way to implement VBA code to query multiple spreadsheets in a directory, pulling data from specific cells into a new spreadsheet, and ensuring all spreadsheets in that directory are included in the query?

Where I work we have a huge amount of test data in .xls format, hundreds of identically formatted spreadsheets in each folder for a specific test. I need to import specific cells from each spreadsheet into a new spreadsheet for trends analysis.

Currenty using Excel 2003, will be migrating to 2007 later this year.

Any help would be greatly apprecieated.

Greg

 

[jbeaucaire]

This is a stock macro I "adjust" for tasks like this. I've highlighted in red the StrPath line where yuo would need to list the path to the folder where your files are...don't forget the final "\" in that string.

Then I highlighted in blue where you would adjust what you want copied.

Sub CollateReportFromFiles()
'Open all .XLS in specific folder and import data (2007 compatible)
Dim strFileName As String, strPath As String, MyVal As String
Dim wbkOld As Workbook, wbkNew As Workbook, ws As Worksheet
Dim NR As Long, LR As Long

Application.EnableEvents = False
Application.DisplayAlerts = False

Set wbkNew = ThisWorkbook
strPath = "C:\Documents and Settings\Jerry\Test\"  'Your path, don't forget the final "\"
strFileName = Dir(strPath & "*.xls")
wbkNew.Activate
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"

    For Each ws In Worksheets
        If ws.Name <> "Temp" Then ws.Delete
    Next ws

ActiveSheet.Name = "Report"
NR = 1

    Do While Len(strFileName) > 0
        Set wbkOld = Workbooks.Open(strPath & strFileName)
        LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
        ActiveSheet.Range("A1:H" & LR).Copy wbkNew.Sheets("Report").Range("A" & NR)
        NR = NR + LR
        strFileName = Dir
        wbkOld.Close False
    Loop
    
ActiveSheet.Columns("A:AA").AutoFit     'adjust if needed
Application.DisplayAlerts = False
Application.EnableEvents = True
End Sub

If you need help with adjustments, remember to provide specific references to the data you want copied...location...formatting yes/no...

My macro creates a brand new "Report" each time it is run, if that's not right we can adjust that, too, so that it appends to an existing report if that's what you prefer.