Excel VBA to import all the data of various files into 1 file in the same folder

Hi,

I have a list of excel (2010 version) files in a folder (eg: c:\temp\work\). The files in this list are all in the same format and contain 1 sheet in each file.

I need a VBA to import the data in each file, found in the folder above, into one worksheet, listing the contents of each file, and then save the new file with the combined data. However, the code needs to be dynamic in the same that the number of files and the filenames change, therefore, the code has to automatically pick up the list of files in the folder.



Can anyone help, please.


regards,
makku

There are a few ways of importing data, but it would help if you could upload a sample workbook of the data you are wanting to extract from as well as a workbook with the desired outcome.

There's a thread here with a couple of approaches. http://www.excelforum.com/excel-prog...atabase-2.html

Basically what i need is a code that opens all the files in a folder, copies all the content (of each file), and pastes into another file which will eventually contain all the info. pasted from the individual files.


regards,

If you can upload a sample workbook as requested, we can have a look, without this, all I can do is point you at the thread above which poses the same question

I think something went wrong when you tried to upload your files, I can't open either of them, could you try and add them again please?

I tried to upload again and i still get the error. However, you can view the files by clicking on the paper clip in the main thread of "excel-programming (http://www.excelforum.com/excel-programming/) next to my post.

Ok Got em , I'll have a look

give this a whirl, you just need to change the path at the top to where you have all your workbooks saved that you are importing. This code goes in the workbook that summarises the data.

Please Login or Register  to view this content.

Thanks. It worked great!

One last question. How can i import the data into an existing file with a particular sheetname "Actual Costs" into cell D4?


regards,

So the same as before but with a different sheet name and the top left cell as D4?

Yes, the same but i would like to get the result in a sheetname called "Actual Costs" and the info. will be pasted starting cell D4 in this sheet.

Depending on what's in the surrounding cells of D4

Please Login or Register  to view this content.

I'm getting a "Run-time error '13': type mismatch on the line:

Please Login or Register  to view this content.

regards,

Could you post a copy of your workbook?

See attached file.

It seems to be working, however, the result is being inserted in the sheet called "Blank1" not in the "Actual Costs" sheet. Please note that this is just an extract of the combined.xlsm file... there are more sheets that i have removed!


regards,

I can't replicate your error, is the actual costs sheet exactly the same as in your real workbook, also I can't see how my revised code can put the data into "Blank1" sheet. Using your workbook, the code works fine for me.

The only change is the starting point for dumping the data. Please try running this in the sample that you uploaded. If it works in that, then the problem lies in your actual workbook

Please Login or Register  to view this content.