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

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

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

I havn't tested this on your particular case, but I know chgdir doesn't
work with network drives very nicely. Possible this is a similar
problem? Might test it by moving the file that's on the S drive right
now to your C drive and then try the macro and see if it works.


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

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

here is a link:

http://tinyurl.com/pa3e3

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote:

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

Hi Tom,

Thank you very much. It seems that the solution is way to complicated,
considering that I was looking for an easy way to define links to
another workbook.

I tried this solution:

Option Explicit

Function filref(Fil As String, Ark As String, Celle As String)
Fil
End Function

Sub Fil(Fil As String, Ark As String, Celle As String)
Dim txt As String
txt = "'S:\ØKA\Budget06\12 linien\" & "[" & Fil & ".xls]" & Ark & "'!"
& Celle
Fil = Application.ExecuteExcel4Macro(txt)
End Sub

But i did'nt work.

Regards,
JanB

Tom Ogilvy skrev:

here is a link:

http://tinyurl.com/pa3e3

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote:

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