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

"A.A.A" <(E-Mail Removed)> skrev i melding
news:6ca41585-3d01-49d2-817b-(E-Mail Removed)...
> 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

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.


"A.A.A" wrote:

> 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
>

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


--

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


"Harald Staff" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>
> "A.A.A" <(E-Mail Removed)> skrev i melding
> news:6ca41585-3d01-49d2-817b-(E-Mail Removed)...
>> 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 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


On Jul 11, 6:03*pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> For the OP
>
> You can also use this add-inhttp://www.rondebruin.nl/merge.htm
>
> See also the code links on the bottom of the page
>
> --
>
> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>
> "Harald Staff" <s...@enron.invalid> wrote in messagenews:(E-Mail Removed)...

Hi Humphrey

Thanks for the feedback


--

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


<(E-Mail Removed)> wrote in message news:d40f21b6-6fc1-427a-aa4c-(E-Mail Removed)...
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


On Jul 11, 6:03 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> For the OP
>
> You can also use this add-inhttp://www.rondebruin.nl/merge.htm
>
> See also the code links on the bottom of the page
>
> --
>
> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>
> "Harald Staff" <s...@enron.invalid> wrote in messagenews:(E-Mail Removed)...