VB alternative to using INDIRECT.EXE?

Before I explain the issue that I am having, I would like to start out by saying thank you to anybody that has the time to assist me with this thread.

Now, let me give you a brief synopsis of our current situation...

I've got a Workbook that has been built to gather/pull data from, what has turned out to be, hundreds and hundreds (and growing) of different closed Workbooks. It's basically a Purchase Order Log that automatically gathers the data from the individual Purchase Orders as they are created.

This PO Log Workbook is a table with 8 columns, A:H.

Column A has the following formula in it.

Please Login or Register  to view this content.

Where "FL" is a Name substitution for the folder location to our individual Purchase Order files. As the formula indicates, it searches that folder location for any existing or new files in that folder, then transfers that file name, minus the extension (.xlsx), into the respective row within Column A.

It is this retrieved data in Column A that drives the remaining formulas located in the remaining columns (B:H). Those remaining formulas all look exactly the same, with exception to the specific cell that they are requesting data from. Columns B:H have the following formula in them.

Please Login or Register  to view this content.

You'll notice that this formula is looking for a file named whatever is in Column A's respective cell +.xlsx. Once that file is located, it is looking for data in Cell F9. Every remaining column is looking for data in a different cell(s), but the rest of the formula remains the same.

That's the meat and potatoes of what should be a very simple Workbook.

What has happened overtime though is that we now have over a thousand individual files that this Workbook is looking into, and it's resulting in exponentially increased calculation times. Right now, we have to wait a little over 30 minutes for our PO Log file to update. And that amount of time is increasing as more and more individual PO files are created.

My question to somebody out there is...

Is there some other way for me to achieve what it is that this PO Log workbook is doing, without having to wait 30+ minutes for it to gather the data from those thousands of "closed" workbooks?

Can VB provide a solution to this?

Any and all help is greatly appreciated. I look forward to any new ideas.

Thank You

I decided to go ahead and attach a copy of this PO Log for reference. I made to sure to remove/rename any specific server location indicators for privacy reasons.

Automated PO Log.xlsm

I figured as much.

You wouldn't happen to have any advice on the best route I could take in somehow using all of this existing data from a 1,000+ separate files to then incorporate into some type of database program.

And as far as database's go, would OpenOffice's "Base" work as an alternative to Access or SQL?

I don't have much experience with database software, but am an extremely quick learner, and already have a decent understanding on how they work.

Well... I got what I was looking for with VBA. Took awhile. But here it is below in case anybody else is looking for a VBA solution to this, outside of a Database application.

Please Login or Register  to view this content.