Could be a wrong version.
Here's the version that worked for me:
Option Explicit
Function pull(xref As String) As Variant
'inspired by Bob Phillips and Laurent Longre
'but written by Harlan Grove
'-----------------------------------------------------------------
'Copyright (c) 2003 Harlan Grove.
'
'This code is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published
'by the Free Software Foundation; either version 2 of the License,
'or (at your option) any later version.
'-----------------------------------------------------------------
'2004-05-30
'still more fixes, this time to address apparent differences between
'XL8/97 and later versions. Specifically, fixed the InStrRev call,
'which is fubar in later versions and was using my own hacked version
'under XL8/97 which was using the wrong argument syntax. Also either
'XL8/97 didn't choke on CStr(pull) called when pull referred to an
'array while later versions do, or I never tested the 2004-03-25 fix
'against multiple cell references.
'-----------------------------------------------------------------
'2004-05-28
'fixed the previous fix - replaced all instances of 'expr' with 'xref'
'also now checking for initial single quote in xref, and if found
'advancing past it to get the full pathname [dumb, really dumb!]
'-----------------------------------------------------------------
'2004-03-25
'revised to check if filename in xref exists - if it does, proceed;
'otherwise, return a #REF! error immediately - this avoids Excel
'displaying dialogs when the referenced file doesn't exist
'-----------------------------------------------------------------
Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long
'** begin 2004-05-30 changes **
'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **
n = InStrRev(xref, "\")
If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)
Else
n = InStrRev(xref, "!")
'changed from '''n = InStrRev(Len(xref), xref, "!")
If n > 0 Then b = Left(xref, n - 1)
End If
'** key 2004-05-28 addition **
If Left(b, 1) = "'" Then b = Mid(b, 2)
On Error Resume Next
If n > 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0
End If
If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
'** end 2004-03-25 changes **
'** end 2004-05-28 changes **
pull = Evaluate(xref)
'** key 2004-05-30 addition **
If IsArray(pull) Then Exit Function
'** end 2004-05-30 changes **
If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp 'immediate clean-up at this point
Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro
On Error Resume Next 'now clean-up can wait
n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)
Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))
If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)
Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1))
Next C
pull = r.Value
End If
CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing
End If
End Function
=======
There was just another thread that said that this worked on some versions of
excel/windows but not others.
What version of excel and windows are you using?
If you're using xl97, then add this to the bottom of the module:
Function InStrRev97(mystr As Variant, mydelim As String) As Long
Dim i As Long
InStrRev97 = 0
For i = Len(mystr) To 1 Step -1
If Mid(mystr, i, 1) = mydelim Then
InStrRev97 = i
Exit Function
End If
Next i
End Function
(and replace instrrev with instrrev97 in Harlan's code.)
Dave,
Something is really strange here. I get the correct result in the cell when
I do not use =pull().
I attempted to copy and paste the formula you have below, but I continue to
get the same result. - #VALUE!
Using the pull() function seems like it is more logical, but the cell only
seems to process when you would normally get #REF!
Is it possible I have grabbed the wrong pull function?
:
I used this formula:
=pull("'"&modeldir&"\[jointdefaults.xls]Boltrows'!$E$5")
And Harlan's UDF worked fine for me.
(I still wanted you to keep the =pull() portion.)
Norm wrote:
Dave,
Still having trouble.
ModelDir = C:\TeklastructuresModels\11.2
Cell which returns the correct result =
"='C:\TeklaStructuresModels\11.2\[jointdefaults.xls]Boltrows'!$E$5"
Cell which returns
"='C:\TeklaStructuresModels\11.2\[jointdefaults.xls]Boltrows'!$E$5" is =
"="='"&ModelDir&"\[jointdefaults.xls]Boltrows'!$E$5""
I have tried variations of this as well
Cell =
"="'"&ModelDir&"\[jointdefaults.xls]Boltrows'!$E$5"" returns
"'C:\TeklaStructuresModels\11.2\[jointdefaults.xls]Boltrows'!$E$5"
:
Dave,
Thanks! That did work, however I need to be able use the value ModelDir for
the path and folder location of the xls file. Is that possible. Your method
leaves me with a static result.
Norm
:
I'd copy that formula to an empty cell.
Then I'd remove the pull( and last ).
Then with the cursor still in the formula bar, hit F9 to convert it to text.
Then add the = sign to the formula.
Did that work ok?
If not, maybe your modeldir isn't correct????
I often open the other workbook, build a formula that points to that cell, then
close that workbook so I can see how excel builds that long string.
You may want to post back both versions -- a direct link and the formula you're
using (and what's in modeldir) if you have trouble.
Norm wrote:
Can someone help me out and explain (show an example formula) of how to use
Harlan's pull function?
It sounds exactly like what I need, but I cannot get it to work. Below is
what I have input into the cell which I need to recover data from a closed
workbook.
=pull("'"&ModelDir&"\[jointdefaults.xls]Defaults'!$C$21")
am I even close?
The spreadsheet is always named jointdefaults.xls and the folder path is
obtained in the range ModelDir.
