INDIRECT.EXT Morefunc.xll addin

[John R]

Has any anyone had success with using this addin?
I have tried it and if the woorkbook is open it works fine just like INDIRECT but if the workbook is closed I get a #REF! error.
Any help or comments would be appreciated.

 

[just_jon]

Hi, hello and welcome to MrExcel -- yes, there are ailures with that on some O/S and version combinations.

Try the PULL function by Harlan Grove --

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> pull(xref <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#007F00">'inspired by Bob Phillips and Laurent Longre</SPAN>
<SPAN style="color:#007F00">'but written by Harlan Grove</SPAN>
<SPAN style="color:#007F00">'-----------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">'Copyright (c) 2003 Harlan Grove.</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'This code is free software; you can redistribute it and/or modify</SPAN>
<SPAN style="color:#007F00">'it under the terms of the GNU General Public License as published</SPAN>
<SPAN style="color:#007F00">'by the Free Software Foundation; either version 2 of the License,</SPAN>
<SPAN style="color:#007F00">'or (at your option) any later version.</SPAN>
<SPAN style="color:#007F00">'-----------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> xlapp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, xlwb <SPAN style="color:#00007F">As</SPAN> Workbook
<SPAN style="color:#00007F">Dim</SPAN> b <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, r <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range, n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

pull = Evaluate(xref)

<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">CStr</SPAN>(pull) = <SPAN style="color:#00007F">CStr</SPAN>(CVErr(xlErrRef)) <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> CleanUp <SPAN style="color:#007F00">'immediate clean-up at this point</SPAN>

<SPAN style="color:#00007F">Set</SPAN> xlapp = CreateObject("Excel.Application")
<SPAN style="color:#00007F">Set</SPAN> xlwb = xlapp.Workbooks.Add <SPAN style="color:#007F00">'needed by .ExecuteExcel4Macro</SPAN>

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN> <SPAN style="color:#007F00">'now clean-up can wait</SPAN>

n = InStr(<SPAN style="color:#00007F">In</SPAN>Str(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

<SPAN style="color:#00007F">Set</SPAN> r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

<SPAN style="color:#00007F">If</SPAN> r <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
pull = xlapp.ExecuteExcel4Macro(xref)
<SPAN style="color:#00007F">Else</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
<SPAN style="color:#00007F">Next</SPAN> c

pull = r.Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

CleanUp:
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> xlwb <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> xlwb.Close 0
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> xlapp <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> xlapp.Quit
<SPAN style="color:#00007F">Set</SPAN> xlapp = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

ex SUM(pull("'"&A1&"\["&B1&".xls]"&C1&"'!"&"e1:e10"))

A1:
C:\Documents and Settings\My Documents

B1:
money2

C1:
sheet1

 

[Ekim]

It would be useful if you posted your formula. In the meantime, here are a few general comments:

INDIRECT.EXT does not work with
• PC’s running Windows Me (Millennium version)
• defined names in closed workbooks.
• Range arguments (it can only return single values)

References:
http://makeashorterlink.com/?O2E512068
http://makeashorterlink.com/?H21621068

In addition, you cannot copy a formula containing INDIRECT.EXT by dragging the formula down/across a range, unless you make a slight adjustment to the syntax e.g. you wish to get data from say cells A1, A2 and A3 from a closed file named Test1.xls, located at C:\Temp3:

B4:
=INDIRECT.EXT("'C:\Temp3\[Test1.xls]Sheet3'!A"&C4)
drag the formula down.

C4 contains 1, C5 contains 2 and C6 contains 3.

Some alternatives to INDIRECT.EXT at http://makeashorterlink.com/?T1D723297 .
The reference also includes an example on how to use INDIRECT.EXT in conjunction with the Sumproduct function (not tested).

Other alternatives here:http://www.mrexcel.com/board2/viewtopic.php?t=67212
Nimrod’s macro works very well.

Other notes:

If you just want to link to a closed workbook/worksheet, you can simply use:

='C:\Temp3\[Test1.xls]sheet1'!A1

If you are doing lookups, Vlookup/HLookup and Index/Match work with closed workbooks (you do not have to use INDIRECT.EXT).

Harlan Grove’s “Pull” function works very well (I use this in a few applications): see Jon's post above.

Warning:
Do not try to drag Mr. Grove’s UDF formula down or across the page. On my PC, Excel “freezes” and requires Ctrl-Alt-Delete to get out of it. Similarly, in one application I have a validation dropdown that houses file names of closed workbooks. If I selected a file name that does not exist, the UDF went into an endless loop. In this case, only a hard re-boot worked (Ctrl-Alt-Delete failed).

Regards,


Mike

 

[John R]

It would be useful if you posted your formula. In the meantime, here are a few general comments:

INDIRECT.EXT does not work with
• PC’s running Windows Me (Millennium version)
• defined names in closed workbooks.
• Range arguments (it can only return single values)

References:
http://makeashorterlink.com/?O2E512068
http://makeashorterlink.com/?H21621068

In addition, you cannot copy a formula containing INDIRECT.EXT by dragging the formula down/across a range, unless you make a slight adjustment to the syntax e.g. you wish to get data from say cells A1, A2 and A3 from a closed file named Test1.xls, located at C:\Temp3:

B4:
=INDIRECT.EXT("'C:\Temp3\[Test1.xls]Sheet3'!A"&C4)
drag the formula down.

C4 contains 1, C5 contains 2 and C6 contains 3.

Some alternatives to INDIRECT.EXT at http://makeashorterlink.com/?T1D723297 .
The reference also includes an example on how to use INDIRECT.EXT in conjunction with the Sumproduct function (not tested).

Other alternatives here:http://www.mrexcel.com/board2/viewtopic.php?t=67212
Nimrod’s macro works very well.

Other notes:

If you just want to link to a closed workbook/worksheet, you can simply use:

='C:\Temp3\[Test1.xls]sheet1'!A1

If you are doing lookups, Vlookup/HLookup and Index/Match work with closed workbooks (you do not have to use INDIRECT.EXT).

Harlan Grove’s “Pull” function works very well (I use this in a few applications): see Jon's post above.

Warning:
Do not try to drag Mr. Grove’s UDF formula down or across the page. On my PC, Excel “freezes” and requires Ctrl-Alt-Delete to get out of it. Similarly, in one application I have a validation dropdown that houses file names of closed workbooks. If I selected a file name that does not exist, the UDF went into an endless loop. In this case, only a hard re-boot worked (Ctrl-Alt-Delete failed).

Regards,


Mike

Thanks for responding.
First of all I AM running Windows ME. Maybe that's my problem.

My formula is as follows:
=Indirect.ext("'c:\my documents\my excel\[" & A2 & " Inv.xls]entry sheet'!C6")
A2 contains a store number ie: 01

Thanks again!

 

[Ekim]

John,

Using Excel XP

Is your file named “01 Inv.xls” (note the space) or “01Inv.xls”?

Your formula works for me when the space before “Inv” is eliminated. For testing purposes, my path is “C:\Temp3\. The file is closed.

=INDIRECT.EXT("'C:\Temp3\["&A2&"Inv.xls]entry sheet'!C6")

In your formula, there is a space in front of “Inv”

=Indirect.ext("'c:\my documents\my excel\[" & A2 & " Inv.xls]entry sheet'!C6")

Accordingly, the formula resolves to “01 Inv.xls” not “01Inv.xls”.

If the space is not the problem, then it appears that your version of Excel will not work with INDIRECT.EXT.

I assume that you have thoroughly checked that your path description is correct, that a worksheet named “entry sheet” exists in 01Inv.xls etc. Note that “ entry sheet” and “entry sheet ” (note the leading and trailing space) is not the same as “entry sheet” (no spaces).

Have you tried Harlan Grove’s “Pull” function?

Regards,

Mike

 

[John R]

John,

Using Excel XP

Is your file named “01 Inv.xls” (note the space) or “01Inv.xls”?

Your formula works for me when the space before “Inv” is eliminated. For testing purposes, my path is “C:\Temp3\. The file is closed.

=INDIRECT.EXT("'C:\Temp3\["&A2&"Inv.xls]entry sheet'!C6")

In your formula, there is a space in front of “Inv”

=Indirect.ext("'c:\my documents\my excel\[" & A2 & " Inv.xls]entry sheet'!C6")

The workbook name is "01 Inv.xls". There is a space between 01 and Inv.
I'm using Excel 97.
I upgraded to Windows XP Home Edition today and now just plain INDIRECT
doesn't work when the workbook is open and it did under Windows ME.
INDIRECT.EXT still doesn't work
I feel like I'm going backwards. I really don't like XP and would like to go back to ME but I guess I'll stick with it.
I'm thinking of trying ACCESS and writing my application from scratch and forget about EXCEL.
Any advise???
Accordingly, the formula resolves to “01 Inv.xls” not “01Inv.xls”.

If the space is not the problem, then it appears that your version of Excel will not work with INDIRECT.EXT.

I assume that you have thoroughly checked that your path description is correct, that a worksheet named “entry sheet” exists in 01Inv.xls etc. Note that “ entry sheet” and “entry sheet ” (note the leading and trailing space) is not the same as “entry sheet” (no spaces).

Have you tried Harlan Grove’s “Pull” function?

Regards,

Mike

 

[hond70]

Quite interesting the Harlan Grove’s “Pull” function.
However I already used the indirect.ext in too many files to change it all.

I am working on a project where we will receive hundreds of questionnaires (some consist of 850 questions...). With some 25 files I pull data (the answers) out of these questionnaires. With that amount of data I needed a very flexible cell reference: filename (name of the respondent + xls), sheetname and even cell column and row.

Uptill now I use:
=IF(ISERROR(SETV(INDIRECT.EXT("'"&Location!$A$1&"["&$C3&".xls]"&$C$4&"'!$"&$F$3&$G3)));"";IF(GETV() <> 0;GETV();""))

Where ISERROR takes care of #REF! (when some questionnaires are not in yet) and GETV() <> 0 takes care of an annoying zero. SETV/GETV makes the formula faster.

I works rather fine, but still I have some problems with the speed and more annoying I sometimes experience an update failure. Changing ISERROR to ISERR or visa versa helps, but that is not a solution. Any suggestions?

 

[John R]

John,

Using Excel XP

Is your file named “01 Inv.xls” (note the space) or “01Inv.xls”?

Your formula works for me when the space before “Inv” is eliminated. For testing purposes, my path is “C:\Temp3\. The file is closed.

=INDIRECT.EXT("'C:\Temp3\["&A2&"Inv.xls]entry sheet'!C6")

In your formula, there is a space in front of “Inv”

=Indirect.ext("'c:\my documents\my excel\[" & A2 & " Inv.xls]entry sheet'!C6")

Accordingly, the formula resolves to “01 Inv.xls” not “01Inv.xls”.

If the space is not the problem, then it appears that your version of Excel will not work with INDIRECT.EXT.

I assume that you have thoroughly checked that your path description is correct, that a worksheet named “entry sheet” exists in 01Inv.xls etc. Note that “ entry sheet” and “entry sheet ” (note the leading and trailing space) is not the same as “entry sheet” (no spaces).

Have you tried Harlan Grove’s “Pull” function?

Regards,

Mike

Sorry I screwed up my last responce by typing in the middle of your responce.
I finally figured out why INDIRECT.EXT wouldn't work for me.
When I upgraded to XP it added 2 new folders to my path to the workbooks. No wonder it couldn't find my workbooks! I corrected the path
and INDIRECT.EXT works like a champ. I still hate XP and am experiencing
many problems.
Thanks again

 

[hond70]

I figured out why the ISERR and ISERROR in the formula was unstable. It had to do with our company network. I don't know what the exact reason is. In any case, when I copied the questionnaires and the files with the indirect.ext formulas to pull data out of the questionnaires to my hard drive, the add in worked flawlessly.
So test your Excel file in a network environment if that's necessary.

Good luck!

 

[hond70]

255 character limitation of indirect.ext and grove's "p

Help, this is a disaster... I discovered that indirect.ext (add in of morefunc) only pulls the first 255 characters of the contents of a cell, when the target file is closed. It does pull out the full 1024 characters when both files are open.
This is not what indirect.ext was designed for.

I also tried Grove's pull-function, but unfortunately this also has the 255 characters limitation.
Only when using the direct
=c:\test.xls[sheet1]!A1
results in a full loaded cell of the max. visible 1024 characters.

I can't open 172 (!) files together to pull data into my file... you would need 2 Gigs of RAM.....
Anybody has a solution? I discovered this after 2 months of work on hundreds of sheets that will pull data out of another hundreds of files (questionnaires). Now I really freaking out!!!!!!!

I use formulas like
=IF(ISERROR(SETV(INDIRECT.EXT("'"&Locaties!$A$1&"["&A11&".xls]"&$C$3&"'!$B$"&$D$3)));"";IF(GETV() <> 0;GETV();""))
and
=IF(ISERROR(INDIRECT.EXT("'"&Locaties!$A$1&"["&A10&".xls]"&$C$3&"'!$B$"&$D$3));"";INDIRECT.EXT("'"&Locaties!$A$1&"["&A10&".xls]"&$C$3&"'!$B$"&$D$3))