Pull numbers from closed workbook

**The Phil** · 09-02-2010, 06:47 PM

I need code that will pull values from a closed workbook. The path will be variable based on the value in a cell. I'm currently using the indirect.ext from the morefunc add-in but I want to get away from that so I don't have to install that add-in for every new person who uses the file.

I attached a mini example of what I want to do. You can see that each box has a carry forward amount that it gets from a closed workbook, a number that it changes by, and then the new total, which will be carried on to the next file. The formula is set up to automatically look 2 spots below its current location so I can cut and paste each box to a new location, or copy a box to a new location and it will work without having to change cell references. The example files are made to work in C:\Temp2\

If anyone can help me make a code that will replace the indirect.ext I would be eternally grateful.

**MarvinP** · 09-02-2010, 09:08 PM

I believe the topic is discussed at:

http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

Hope that helps.

**The Phil** · 09-03-2010, 03:32 PM

I tried that site but it didn't seem to work. I copied the code into a new module, and then used the formula: =getvalue(H3,H5,"Sheet1","B5") where H3 is the cell with the path, H5 is the cell with the filename, Sheet1 is the sheet and B5 is the cell in the other workbook I want to retrieve.

I'm using the second file from my previous example to try to get a number from temp1.xls in C:\Temp2 and I keep getting a #VALUE error. I attached my new attempt if anyone can have a look?!

Thanks

**The Phil** · 09-07-2010, 11:19 AM

bump....!?

**NBVC** · 09-07-2010, 11:31 AM

Have a look here for alternatives..

http://www.dailydoseofexcel.com/arch...sed-workbooks/

Harlon Grove's Pull() UDF see link provided in the above link works just like INDIRECT.EXT() once you added it to your VBA module.

**The Phil** · 09-07-2010, 06:41 PM

Thanks! That Pull() function looks like exactly what I need! I'll play around with it tomorrow and see if I can get it to work. I'm surprised that it's a 6 year old code. Can we expect these things to still work if we update to Excel 2010?

**NBVC** · 09-08-2010, 07:45 AM

I think it would still be compatible.. but it is also well known that it can be quite inefficient... especially if you are using it alot in the workbook.

**The Phil** · 09-09-2010, 06:53 PM

Wow, it's a lot slower than indirect.ext. I have the formula about 16 times in the workbook and with indirect.ext when I update a cell, it recalculates in a fraction of a second. I replaced all of the forumlas with the PULL() formula and it worked, but it takes 4 seconds to recalculate every time I enter a value into a cell. It looks like I may be sticking to indirect.ext. My office doesn't use Office 2010 yet, but does anyone know if the morefunc add-in works with it?

Thanks for the links anyways, it is good that I at least got to try the VBA replacement.

**NBVC** · 09-09-2010, 09:22 PM

I know it is compatible with 2007... but it last I knew it did not allow for embedding into the workbook for sharing, like the 2003 version did... not sure if that was ever fixed.