Re: Help for Harlan's Pull

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.)

Norm wrote:
>
> 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?
>
> "Dave Peterson" wrote:
>
> > 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"
> > >
> > > "Norm" wrote:
> > >
> > > > 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
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > 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 spread*** is always named jointdefaults.xls and the folder path is
> > > > > > obtained in the range ModelDir.
> > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson
.