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
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.
My VBA skills aren't good enough for me to experiment so I'm hoping someone can tell me if the following is possible
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.
