As I recall, ExecuteExcel4Macro doesn't work in a function used in a
worksheet (User defined function - UDF) as you define you are using the
function. Try my correction in VBA calling it with a sub. If you are
looking for something to replace indirect so you can access a closed
workbook, you are out of luck unless you want to use a method posted by
Harlan Grove where the function creates a separate instance of Excel, opens
the file, gets the information and closes it all down. If you want to go
that route, post back and I will try to find a URL for you.

You can horse around with moving the file, but chdir doesn't have any
problems with mapped drives.


--
Regards,
Tom Ogilvy



"JanB" wrote:

Hi Tom,
Thanks for your quick response. Your suggestion did'nt help. I also
tried to remove the "+" in the reference.
Anyother suggestions.
Regards
Jan

Tom Ogilvy skrev:

as a minimum, you would need to change
filref = Application.ExecuteExcel4Macro("txt")

to
filref = Application.ExecuteExcel4Macro(txt)

--
Regards,
Tom Ogilvy


"JanB" wrote:

Hope somebody is able to help me on this small problem.
I am trying to return a cellvalue from a closed workbook using a
function.
The code that I've used is as follows:

Function filref(Fil As String, Ark As String, Celle As String) As
String
Dim txt As String
txt = "+'S:\B\HYT\12 linien\" & "[" & Fil & ".xls]" & Ark & "'!" &
Celle
filref = Application.ExecuteExcel4Macro("txt")
End Function

In the cell that should return the value then contains:
=filref("Amager","Brug","B2")

The result in the cell is: #Value!

Could it be that the ExecuteExcel4Macro is not installed or should the
function in some way be activated and how could this be done.

Regards
JanB