Macro that checks multiple closed workbooks and returns value for each to cell in openbook

[RichTea88]

Good Day all,

I'm a document controller and have multiple workbooks which contain document details, including the various dates that documents are due for submission. I deal with many projects and each have a workbook of their own. To help my manage all of these I have a workbook which contains hyperlinks to all the workbooks in one column and in another I put the next due date for that project. This way I don't have to check through all the workbooks everyday.

I was wondering if it was possible to write a macro which scans through all the open projects extracting the hyperlink to each one, then goes to that workbook, scans for the next document due date, stores the value and then returns it and stores it in the row for that project. Without opening all the workbooks.

I considered having a macro that checks for the next due date in each individual register then stores it in a cell and that way I can write another macro which in turn simply checks that cell of each workbook and returns the value, but I think that would mean I would have to open each workbook to update this cell.

This one is a big ask, so I figured it would be best to get some advice from the pro's before attempting to write something ridiculously complex myself. Any idea on the best way to do this would be greatly appreciated. Rep as always.

Cheers,

R

[ragulduy]

Best I can think of would be something like:

Please Login or Register  to view this content.

I'm not sure you can do this without opening all the workbooks.

[JBeaucaire]

Since you can use FORMULAS to extract data from a closed workbook, you could possibly construct a macro to write the formulas for you. Once you constructed the string fully to recreate the formulas you've designed that pull the data from a known workbook name, that formula could be tweaked by VBA to use the found workbook name and enter that new formula in the row for that workbook.

That would work without opening the workbooks.

THe only thing you need to do is construct the first lookup formula manually for a known workbook name.

[RichTea88]

Hi Jerry,

Would this be using a VLOOKUP formula? I'm thinking that this type of Macro writing might be out of my skill set. But my job is pretty boring so i'd be as well give it a shot. I'll post what I come up with here when I've got something vaguely resembling what I'm after.

Cheers

[JBeaucaire]

VLOOKUP is a formula that works on closed workbooks, so you could use that, as well as INDEX/MATCH and others.

The trick first is to get the formulas setup normally in a sample row of data, then close the reference workbook. Now your cells will include a full path to the workbook and sheet and cell ranges being reference.

Now, turn on the macro recorder and on each formula cell, press F2 to open the edit window, then ENTER. Repeat with each cell. Turn off the macro recorder.

Look at the code, you now have a VBA version of those formulas and the columns they go in. From here we start the process of figuring out how to "insert" a different filename into those formulas and writing those formulas into a new row.

[RichTea88]

Something like this?

Please Login or Register  to view this content.

Where RowNo is the row destination in the open workbook
Address is the hyperlink for the closed workbook extracted from that row, transmittal register is the sheet and then the range.
Apologies, I'm very inexperienced with this. would it help if I uploaded an example of the workbooks that I'm using?
My concerns are that even though the workbooks I'm looking up are all formatted the same, for example the sheets will all be called 'Transmittal Register' and the range will all be in the same column. But the number of rows will be different and even then the macro will have to check to what documents are 'open' and 'closed' and then determine out of the open ones which date is next due.
I always do this, come up with an idea then find out just how complex it is going to be to program :/ Apologies if I'm wasting yout time. By all means tell me if this is too complex, unless you like a good challenge! :P

Cheers for all your help so far!

[RichTea88]

I'll give a better explination of what I'm trying to achieve. Bare with me on this!

The 'Quick Access' workbook contains: Column A - client names and hyperlinks to client folders, Column B - project number and hyperlink to project folder and column C - project name and hyperlink to master document register workobook for that project. column D is a simple box to let me know if I should check the document register or not and Column E is when the next document is due for that project. I usually have around 20 - 30 projects to manage documents for.

The MDR - Ignore the first 3 sheets and macro's already written in here. The 'Transmittal Register' sheet is what the 'Quick Access' will be checking for dates. On this sheet the two columns that need checking are column I - the date documents are due for return or for issue and column L - has an X in it if this has already been accomplished. (i.e. the document has already been returned before the due date)

The macro in Quick access would be a refresh button that when I click would perform the following steps (more or less)
1. count to see how many projects there are.
2. check to see if that project is currently operating (by checking to see if theres an X in column D)
3. If it is - extract the hyperlink from column C which links to the MDR for that project
4. In that workbook count to see how many transmittals there are.
5. Check each row to see if column 'L' has an X in it, if it does check next row, if it doesn't store the date from column 'I'
check through all transmittals(rows) and determine out of the 'open' transmittals which date is closest to todays date.
6. Return that date and store it in Column E of the 'quick access' workbook for that project.
7. repeat until all operating projects have been checked and dates returned.

This probably sounds way more confusing than it is. The attached workbooks might give a bit more clarity I hope!

Cheers

R