Achieve same thing as Indirect Function, with closed files

[danrofohio]

I have a situation where I want to refer to cells in many different workbooks. The Indirect function will work if I have all of the external workbooks open, but I have far too many to open at the same time. Is there some way accomplish the same thing without manually entering all of the external reference formulas? In other words, I want to use a formula that gets the file name from another cell.

Thanks,
Dan R.

 

[chirp]

not sure if this is what you are looking for, but something like this might work.

cant find where i originally found the "getValue" function...somewhere on Chip Pearson's site (is modified a tad).

Function pathEx(path As String) As Boolean

On Error Resume Next
pathEx = dir(path, vbDirectory) <> vbNullString
End Function

Function getFile(path As String, Optional tst As Boolean) As String

On Error GoTo exitFunc
tst = False
If Right$(path, 1) = "\" Then GoTo exitFunc
getFile = Right$(path, Len(path) - InStrRev(path, "\"))
tst = True
exitFunc:
End Function

Function getFold(path As String, Optional tst As Boolean) As String

tst = False
On Error GoTo exitFunc
getFold = Left$(path, InStrRev(path, "\") - 1)
tst = True
exitFunc:
End Function

'got this from cPearson
Function getValue(fullPath As String, wsName As String, _
                            rngAd As String, _
                            Optional tst As Boolean) As Variant
'   Retrieves a value from a closed workbook
    tst = False
    On Error GoTo exitFunc
'   Make sure the file exists
    If Not pathEx(fullPath) Then getValue = "": Exit Function
'   Make sure is an excel document
    If Not fullPath Like "*.xl*" Then Exit Function
'   Execute an XLM macro
    getValue = ExecuteExcel4Macro("'" & getFold(fullPath) & "\[" & getFile(fullPath) & "]" & wsName & "'!" & _
    Range(rngAd).Cells(1).Address(, , xlR1C1))
'   No error so tst=true:
    tst = True
exitFunc:
End Function

paste that into a standard code module
if you supply that with the full workbook name, the sheet name, and the address of the cell you want it should work.

 

[F&s anaylst]

Hello to the Forum,

there are a number of ways to do this,
Install the MOREFUNC addin (google for Indirect.Ext) this works on closed workbooks or you could use sumproduct formula instead

 

[chirp]

the method i posted does not work as a UDF btw

 

[danrofohio]

@kevin,

I read some articles on the web about the morefunc addin that said they won't work with Office 2010. What is your experience with that?


Dan

 

[F&s anaylst]

Hi Dan,

sorry not used it on 2010, so you can either use sumproduct or the VBA code that chirp provided