Returning cellvalue from closed workbook using a function

  • Thread starter Thread starter JanB
  • Start date Start date
J

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
 
as a minimum, you would need to change
filref = Application.ExecuteExcel4Macro("txt")

to
filref = Application.ExecuteExcel4Macro(txt)
 
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 said:
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
Click to expand...
Click to expand...
 
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


JanB said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
here is a link:

http://tinyurl.com/pa3e3

--
Regards,
Tom Ogilvy



Tom Ogilvy said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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:
Click to expand...
here is a link:

http://tinyurl.com/pa3e3

--
Regards,
Tom Ogilvy



Tom Ogilvy said:
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



JanB said:
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
Click to expand...
Click to expand...
Click to expand...
 
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:
Click to expand...
here is a link:

http://tinyurl.com/pa3e3

--
Regards,
Tom Ogilvy



Tom Ogilvy said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top