adding 1000 excel files

  • Thread starter Thread starter A.A.A
  • Start date Start date
A

A.A.A

Hi all,

Is there an excel macro thta can merge 1000 excel files? I have 1000
excel files named measure1,measure2,.....,measure1000. Each file
contains one number in the cell "A2". I want to merge these files in
one excel file so that this excel file has just one column and 1000
rows displaying the 1000 numbers. Is this possible with
excel??????????????????/

Aya
 
Hi Aya

A macro like this should do the trick, assuming all the files are stored in
C:\Temp and suffixed xls:

Sub MergeFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
Dim R As Long
MyPath = "C:\Temp"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
DoEvents
R = R + 1
ThisWorkbook.Sheets(1).Cells(R, 1).Value = _
wb.Sheets(1).Range("A2").Value
wb.Close
TheFile = Dir
Loop
End Sub

HTH. Best wishes Harald
 
Try Data > Consolidate with the options...

Function: Count
Reference: *.xls!A2
Create Links: Check

Browse for the folder containing the files then just click cancel to make
sure that it's the current folder.

Click OK to run, when it's finished unhide the rows.
 
Hi Ron

I just want to say that your add-in is brilliant - I have been
searching for ages for such a utility as I have spent hours and hours
extracting data from individual Excel questionnaires, now your add-in
can do it in seconds. Your web site, now discovered, is in my Excel
favourites.

Many thanks

Humphrey
 
Hi Humphrey

Thanks for the feedback


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Hi Ron

I just want to say that your add-in is brilliant - I have been
searching for ages for such a utility as I have spent hours and hours
extracting data from individual Excel questionnaires, now your add-in
can do it in seconds. Your web site, now discovered, is in my Excel
favourites.

Many thanks

Humphrey
 
Back
Top