Gather data from different workbooks

Joe9238

Board Regular
Joined
Jul 14, 2017
Messages
67
Hey,
I have multiple files in multiple subfolders that have data in them that I would like placed into rows along in the workbook the macro is run from.

A couple points to note are:
-The files are all going to be xls files of some sort
-The folders and subfolders will contain various other files like .doc or .txt that should be skipped
-Some of the files may be corrupt or protected
-There are hundreds of files and many subfolders
-The files will contain a number of sheets, the one with the data in it is called 'QUOTE'
-Along the top I need the titles of the cells the data has come from and the data to be organised like this:

B7 l B8 l B11 l B13 l
----------------------
1 l 1 l 1 l 1 l
----------------------
2 l 2 l 2 l 2 l

Any help is appreciated
Thanks in advance :)
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
-The files are all going to be xls files of some sort

What version of Excel are you using? 2003? If any files are XLS, you should update them to XLSX or XLSM(macro enabled).
PowerQuery can do most of what you're looking to do. (I'm not sure how the password is handled for protected books as I haven't done that yet.)
Corrupt files? Fix them or exclude them.

How consistent is the data placement in each worksheet?

Regardless of which method you proceed with, VBA or PowerQuery, you will probably have a long wait on execution. Both methods will end up waiting on the opening and closing of each file that meets your criteria.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top