Data collection

[Sean]

I have hundreds of spreadsheets created from the same
template. They contain different amounts of records (a
record equals a row in this case). One spreadsheet may
have two records (rows); another may have fifteen records
(rows). Each spreadsheet is an individual file, and all
files are stored in the same folder. I need to pull data
from specific columns in each of these spreadsheets (files)
into an EOM report. The first column contains a non-unique
number (like a department number). Although the number may
appear many times in a single spreadsheet, and in many of
the spreadsheets, it only need appear once in the EOM
report. Then in another column, one of three values may
appear, and I need to count, per department number, the
number of times each of these values appear. I am using
Excel 97 on Windows NT4 w/SP6. Also, I would like the
macro/script(?) to open and close each of the spreadsheets
rather than having to manually do it.

 

[Bernie Deitrick]

Sean,

The macro below will combine all the files into one sheet. Then use a pivot table to get your data reports out of the combined
table. Note that the column headings (if you have them) will be copied each time, so you'll need to sort your table and delete the
extra headings prior to doing your pivot table. Also, the macro assumes that your table starts in cell A1, and is contiguous - no
completely blank rows or columns. If you need help modifying the code, post back.

HTH,
Bernie
Excel MVP

Sub Consolidate()
With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set Basebook = Workbooks.Open(.FoundFiles(1))
For i = 2 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
Range("A1").CurrentRegion.Copy Basebook.Worksheets(1).Range("65536").End(xlUp)(2)
myBook.Close
Next i
Basebook.SaveAs Application.GetSaveAsFilename("Consolidated file.xls")
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

 

[Sean]

Bernie,
Thank you so much! That macro will be a huge help. I will
post back if I have difficulty.

Thanks,
Sean