Accessing cells in a closed workbook

[Zenkai]

I need to be able to access cells in a closed workbook based on strings that contain the filename, sheet name, and cel range. I'd like to do this in a function, eg:

A1 = "C:\"
A2 = "Book1.xls"
A3 = "Sheet1"
A4 = "A1"

A5 = "GetClosedData(A1, A2, A3, A4)"

And return whatever was in that workbook. I found out how to do it using a subroutine, but I really need it in a function.

I used the following code to make a function, but when used in a worksheet, this function returns "":

Private Function GetInfoFromClosedFile(ByVal wbPath As String, wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

Anyone have any thoughts? Thanks in advance,

-Nick

 

[SkipVought]

Hi,

Can only be done from a spreadsheet with a formula like this
[tt]
Cells(1, 1).Formula = "='C:\My Documents\[StackedChart.xls]Source'!A1"
TheValue = Cells(1, 1).Value
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884