Macro/Formula to find cells in multiple files

[erniepoe]

Hi All,

So I have a folder with about 50 files that represents the month's accruals, and the cells I am after are in F61 and F68 of the "template" worksheet in every file.

If there is a way I could quickly do this, rather than having to go in and open up 50 files and manually add the cells together, that would be great.

I would not mind setting up a template, for instance, and writing the name of the file if there were a formula that could go in and find it.

Any help is much appreciated, even if you can merely point me to a source where I might be able to teach myself how to do this.

Thanks,
Ernie

 

[erniepoe]

any ideas?

 

[USCguy09]

you could indirect closed workbooks then specify the cells you want.
http://www.dicks-blog.com/archives/2004/12/01/indirect-and-closed-workbooks/

to ge the file names as text, you can do that in a cmd.

1. Open up your command prompt
2. Navigate to the directory which has the files and type the following command -
DIR /B /O:N > filename.txt
This will save all the filenames in the current directory to a text file. The text file will be saved in the same directory.

then copy and paste that into excel