Need to import data from various files within a directory

[kpal29]

My code starts by creating a file called ICB_All.xls
Then I need to look in the directory where this file was created and import the data from all other files within the directory into ICB_ALL.XLS.

So the order of things:
1) create File1, File2, File3 in C:\My documents
2) Create ICB_All.xls in C:\My documents
3) Close all other files except ICB_All.xls and import the data from File1, then underneath that import the data from File2, etc. so that all data is merged into one file.

All datat is in the exact same format. I have tried several different codes to get file names and loop through files but nothing has worked so far. Please help.

 

[fumei]

Have you looked at thr FileSystemObject?

Gerry

 

[PHV]

You may also consider the Dir function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kpal29]

Could you give me an example of using FilesystemObject. I have looked it up but could not find much info.

 

[fumei]

Did you look up Dir as well, as in many cases it is actually easier to use.

1. You need a reference to Scripting Runtime.

2. Create a FileSystemObject

3. Create a FileSystemObject folder

4. Create your files. You did not specifically ask about this, so I am assuming that you can handle this part.

5. loop through the folder using For Each, as in For Each file in Folder, append the contents to the end of the open file - in this case, ICB_ALL.XLS.

Break it down into steps. Use the macro recorder to get each step. Give it a shot, and when you get into specific problems, post your code here.


Gerry