VLOOKUP style Macro to search multiple excel files and return data to a central spreadsheet?

FrankyGrouse

New Member
Joined
Mar 26, 2015
Messages
4
Hi all,

My VBA skills aren't good enough for me to experiment so I'm hoping someone can tell me if the following is possible :confused:

In short, I have inherited a large number of excel documents from which I need to collate a report. I can do so manually but obviously I'd prefer it if excel could do the leg work for me!

All my excel docs have a common layout (i.e. cell A3 will contain a date in all the files, A6 will contain a product code in all the files etc.) and each file has a coded file name i.e. ABC1.xlsx, ABC2.xlsx.

If I were to create a master report spreadsheet, with the file names listed in one column, would it possible to write an excel macro that searches a folder for the file name (i.e. searches C:\example\data for ABC1.xlsx), finds the file with the corresponding file name (ABC1.xlsx), finds a cell (say A6) within the file ABC1.xlsx and then copies the data into the master report spreadsheet?

Essentially I guess I'm looking for a VLOOKUP style function; a macro (or even just a formula?) that searches for a LOOKUP value and returns the data from an specified cell - but the data is spread across a large number of excel files within a folder - is such a thing possible?

Thanks in advance for any advice - let me know if the above is unclear and I'll attempt to expand.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,141,125
Messages
5,704,440
Members
421,349
Latest member
Santhosh3188

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
Top