Getting value from closed workbook

[Dyawlak]

I have found the following function :

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = 0
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address()


' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function


in my cell I have a formula that calls a function call Calc_Calls

Function calc_calls(fn)
file = fn & ".xls"
path = "\\sth-data\Ops Data\CSATS"
calc_calls = GetValue(path, file, "Report", "C7")
End Function


the value in fn is 07042004

The result of the arg value (from GETVALUE function) is '\\sth-data\Ops
Data\CSATS\[07042004.xls]Report'!$c$7 the function returns 0 if the file
does not exist which is correct - but #Value error when a file exists. I
have pasted the result of ARG directly in to a cell (preceeding with =") and
it works fine.

Any ideas ?

Many thanks

 

[Dave Peterson]

This looks suspiciously like the code on John Walkenbach's site:
http://j-walk.com/ss/excel/eee/eee009.txt

And it can't be used in a cell on a worksheet--even if you try to bury it in
other functions (xl is too smart).

But Harlan Grove posted a function that opens another instance of excel and then
retrieves the value from that closed workbook.

http://google.com/[email protected]

I have found the following function :

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = 0
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address()

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

in my cell I have a formula that calls a function call Calc_Calls

Function calc_calls(fn)
file = fn & ".xls"
path = "\\sth-data\Ops Data\CSATS"
calc_calls = GetValue(path, file, "Report", "C7")
End Function

the value in fn is 07042004

The result of the arg value (from GETVALUE function) is '\\sth-data\Ops
Data\CSATS\[07042004.xls]Report'!$c$7 the function returns 0 if the file
does not exist which is correct - but #Value error when a file exists. I
have pasted the result of ARG directly in to a cell (preceeding with =") and
it works fine.

Any ideas ?

Many thanks