adding 1000 excel files

[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

 

[Harald Staff]

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

 

[Lori]

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.

 

[Ron de Bruin]

For the OP

You can also use this add-in
http://www.rondebruin.nl/merge.htm

See also the code links on the bottom of the page

 

[HMKlinkenberg]

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

 

[Ron de Bruin]

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