How to combine data from 100 similar Excel files

[Satish]

Am using Excel 2007 version.

I had posted an excel file (with 10 questions with 4 options for each
question, drop down option is used) to about 100 customers to fill the
answers and post it back to me.

So, now i have 100 excel files. I need to combine all those into one excel
sheet so that it will be easy to analyze, segregate and for further
processing.

So how to combine the data available in these excel sheets.

Please explain the solution 'clearly' such that a layman can understand.

Thanks in advance

 

[Mike H]

Hi,

First put them in a single directory. Put this code in a new workbook by
using Alt+F11 to open VB editor. Righjt click @This Workbook' and insert
module. and paste the code in on the right. It will open every workbook in
your drrectory, copy sheet1 and paste it to your new workbook.

Sub LoopThrough()
Application.DisplayAlerts = False
'Change this to your directory
myPath = "C:\"
ActiveFile = Dir(myPath & "*.xls")
Do While ActiveFile <> ""

Workbooks.Open Filename:=myPath & ActiveFile
'Here is the line that calls the macro below, passing the workbook to it
DoSomething ActiveWorkbook
ActiveWorkbook.Close savechanges:=False
lastrow = Sheets("Sheet1").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A" & lastrow + 1).PasteSpecial
ActiveFile = Dir()
Loop
Application.DisplayAlerts = True
End Sub

Sub DoSomething(Book As Workbook)

Sheets("Sheet1").Range("A1:A10").Copy

End Sub


Mike

 

[Ron de Bruin]

hi Satish

See also
http://www.rondebruin.nl/merge.htm