Combine ranges A1:B100 for all xlsx files in a folder into a new file

[billj]

Hi everyone,

I have a folder which contains over 100 Excel files (all with extension xlsx). Now I want to extract data for the range A1:B100 for each of these files and put them into columns A:B of a new file. So in the new file, range A1:B100 is for the 1st Excel file, A101:B200 is for the 2nd Excel file, A201:B300 is for the 3rd Excel file, and so on.

I can do it manually but this will be really take a long time by doing copy and paste.

I did some search online and there is something called CreateObject("Scripting.FileSystemObject"). But I really don't know how to do with that.

Thanks in advance for help.

[NickyC]

Hi

In order to copy the contents of files you will have to open them all, I think.

Try this macro. It will open each workbook in the directory you specify and copy range A1:b100 into your active workbook, then close the source file.

replace C:\MyFolder\ with your source folder (include the final \ )

Please Login or Register  to view this content.

[billj]

Many thanks, NickyC.

Just wondering if there is a way that we don't need to open all these Excel files in the folder. For my case, the computer will crash if I have to open all these 100+ files.

Thanks everyone.

[billj]

No one can really deal with this question?

[scottiex]

Are you just assuming it crashes or does it actually crash? If so at what point does it crash?

As a quick fix to crashing I put a message in my code to stop it every X files.

I have a couple of computer setups one that has an issue and one that doesn't with doing large numbers of these files like this not clear exactly why.

[NickyC]

It is possible to link to unopened cells, but I think it only works one cell at a time.
try this website for some tips:

http://spreadsheetpage.com/index.php...a_closed_file/

[jolivanes]

Have you tried the code?
As scottiex mentioned, it sounds like you assume that because the code closes the workbooks after it copied the data.

Here are two more, similar to the code from NickyC.
The workbook with the code in it needs to be saved in the same Folder where the other workbooks are saved.

One does a copy and paste while the other pastes the values only

Please Login or Register  to view this content.

Please Login or Register  to view this content.

If your computer does crash, let us know and it can be done with formulae

[jindon]

If the worksheet names that you are reading from are common then
one way

Please Login or Register  to view this content.

[billj]

Hi Jindon, your code is really like a magic. It is neat and so powerful. Certainly it is very useful to me (save me a lot of trouble by doing copy/paste manually).

At the same time, many thanks to NickyC and jolivanes. Your code are useful for me to learn and apply.

I hope the macros here can be useful to others too. Really thanks to the many help from the VBA gurus in this forum

[billj]

And the macro by Jindon doesn't require the source data files to be opened/ closed. It is very fast to run. Amazing!

[jindon]

You are welcome and thanks for the feedback.