D
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
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