Hello
I am trying use Harlan Groves Pull function to get values from a closed
worbook. I had seen some basic instructions for its use but some how I cant
get back to them or get the UDF to work
The code is:
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.
'-----------------------------------------------------------------
Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, c As Range, n As Long


pull = Evaluate(xref)


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

The file/path of the target cell is
C:\Mypath\[MyFile - Winter 2005.xls]forecast'!AE7

In the open workbook AG3 = Winter 2005

I am having trouble constructin the formula. this is what i have and is
returning #ref!
=pull("'"& LEFT(CELL("Filename",A1),FIND("-",CELL("filename",A1))+1) & AG3
&".xls]Forecast'!AE7"). What am I dong wrong?
Thanks!