Returning cellvalue from closed workbook using a function

[JanB]

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

 

[Guest]

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

to
filref = Application.ExecuteExcel4Macro(txt)

 

[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

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

 

[John Fuller]

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.

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

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

 

[Guest]

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

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

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

 

[Guest]

here is a link:

http://tinyurl.com/pa3e3

--
Regards,
Tom Ogilvy

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

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


:

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

 

[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

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

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


:

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

 

[Tom Ogilvy]

the reason the other is so complicated is because the method you are trying
doesn't work (as I stated although probably not a positively as I should
have).

--
Regards,
Tom Ogilvy

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

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

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


:

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