Hi Smita,

See Ron De Bruin's sample code at:

http://www.rondebruin.nl/ado.htm#folder


---
Regards,
Norman



"Smits" wrote in message
ups.com...
Dear all,
This would be my first encounter with people - geniuses in excel
programming. I have 1% knowledge in visual basic, specifically in
excel.

Help req for the following:
There is one destination file named Sales Tracker (EXCEL FILE)
There are 'n' no of source file, for e.g. Company XYZ, Company ABC,
Company PQR, Company XXX (EXCEL FILES)

Task is to capture data from source file {Company XYZ , Company ABC,
Company PQR, Company XXX (EXCEL FILES)} - Range ("C6") sales amt along
with the name of the company appearing in range ("C2") and transpose it
to destination file Sales Tracker.

Sample: Source File 'Xyz file'
A B C
1
2 XYZ
3
4
5
6 100

Sample: Destination File 'Sales Tracker
A B C
Name Sales
1 XYZ 100
2 ABC 200
3 PQR 250
4 XXX 30

The way automation req is:
Destination file shall always be open .
1} Once we show a location of folder where all source files are stored.
It should count the no of files stored in it and a loop that many no of
times, it should be devised in such a manner that it opens a source
file, copies from the currently opened source file and pastes data in
destination file and closes the source file. This, loop should
continues till all the files present in folder are tracked in
destination file Sales tracker.

It has been a tedious task for me.
Request for help....

Thnx ^ n

Smita :)