Looking for an excel expert - need help with "data mining"?

Question

Does anyone know if it is possible to extract data from numerous excel files without opening each? I have a few hundred files that I need 10 pieces of info from. The data is in the same cell in each file. I don't want to have to open each file and try to link to an external file. Can it be done?

Answer 1

It can be done relatively easily, but you'd probably need a tool like the one from Crystal Reports or EMANIO Insight!

Answer 2

Yeah, it's possible...depends on what you need, and if it is in the same place on each file.

You have to do this using a VBA Macro. The best way to do it is to make sure all workbooks are in the same folder, then make sure the information is in the same range of cells on each worksheet, or that the information can easily be distinguished from other info.

The way I would do it is set up a macro that would open each file in the folder, then copy the range out of it to a new workbook and label the range appropriately (if need be...if you just want raw numbers and don't care where they come from, all the easier).

I've written a few programs that do exactly this. If you need more help with it, feel free to message me and I might be able to send you a script or two. It isn't as scary as it might seem...