I am using Excel 2003 and have a master workbook that has many
references (links) to various other workbooks that are in Excel 4.0
format. These source files are created from a query each month.
Depending on the time of the year the source workbook may not exist
yet. I wrote a function (fileexists) in VBA that checks for the
exisatence of the source file. If it exists the function returns true
and then the I use the link to pull in the value to the master
workbook. If it doesn't exist I put a space in the cell instead. Here's
an example from a cell in the master workbook:

=IF(fileexists("managerstmtmo01.xls"),'[managerstmtmo01.xls]managerstmtmo01'!$D$10,"
")

This all works great except I've noticed one problem. If the cell in my
master workbook was once updated because the source file existed, but
then I've renamed or deleted the source file, the value it originally
pulled in from the source file remains in the master workbook's cell.
The only way I can remove the previous value is to edit each cell's
function. When I do that Excel tries to update the link value and I
cancel that, then the cell is blank. Is there a way to have Excel
recalculate each cell and put a space in the cell if the source file
doesn't exist? When I open the master workbook I have it Update the
links then it tells me there are links that can't be updated (because
the source file doesn't exist). Regardless of either telling Excel to
continue or edit the links and not update them, the same thing happens.
The cells in the master workbook are not changed. I've also set Update
Remote References to yes and Save External Link Values to no on the
Calculations tab of Options. Can I do what I'm trying to do or is there
another way to accomplish this?

Thanks.

wrote in message
ups.com...
I am using Excel 2003 and have a master workbook that has many
references (links) to various other workbooks that are in Excel 4.0
format. These source files are created from a query each month.
Depending on the time of the year the source workbook may not exist
yet. I wrote a function (fileexists) in VBA that checks for the
exisatence of the source file. If it exists the function returns true
and then the I use the link to pull in the value to the master
workbook. If it doesn't exist I put a space in the cell instead. Here's
an example from a cell in the master workbook:


=IF(fileexists("managerstmtmo01.xls"),'[managerstmtmo01.xls]managerstmtmo01'
!$D$10,"
")

This all works great except I've noticed one problem. If the cell in my
master workbook was once updated because the source file existed, but
then I've renamed or deleted the source file, the value it originally
pulled in from the source file remains in the master workbook's cell.
The only way I can remove the previous value is to edit each cell's
function. When I do that Excel tries to update the link value and I
cancel that, then the cell is blank. Is there a way to have Excel
recalculate each cell and put a space in the cell if the source file
doesn't exist? When I open the master workbook I have it Update the
links then it tells me there are links that can't be updated (because
the source file doesn't exist). Regardless of either telling Excel to
continue or edit the links and not update them, the same thing happens.
The cells in the master workbook are not changed. I've also set Update
Remote References to yes and Save External Link Values to no on the
Calculations tab of Options. Can I do what I'm trying to do or is there
another way to accomplish this?

Thanks.


Why not create a User Defined Function that does this? In the example, you
have to enter the text "managerstmtmo01.xls" twice. You could write a
function that takes workbook name, worksheet name, row number and column
number as parameters. Build your reference from within the function itself
and use Application.Evaluate to get the value in the external sheet.

I found something that made it possible to refer to external sheets even if
they are not open. You have to use the ExecuteExcel4Macro to do so.

Search the Excel newsgroups for this word and I think you will find
something useful

7Fredrik

I actually started to do this after I posted this question yesterday. I
seem to be running up against another problem now. I have the following
procedures/functions in my workbook. I'm using ExecuteExcel4Macro
because I don't know if the source workbook will be open when this
workbook is open. Here's what I have:

---------------------------------------------------------------------------------------------
Option Explicit

Function FileExists(fname) As Boolean
' Return TRUE if the file exists in current directory
Dim Sep As String
Dim ThisDocsFullName As String
Dim ThisDocsPath As String
Dim SearchDocsFullName As String

On Error Resume Next

Sep = Application.PathSeparator
ThisDocsFullName = Application.ActiveWorkbook.FullName
ThisDocsPath = Left(ThisDocsFullName, InStrRev(ThisDocsFullName,
Sep))
SearchDocsFullName = ThisDocsPath & fname
FileExists = Dir(SearchDocsFullName) < ""
End Function

Sub TestFileExists()
Dim name As String
Dim sheet As String
Dim ref As String
Dim test As String

name = "managerstmtmo01.xls"
sheet = "managerstmtmo01"
ref = "D10"
test = GetSourceData(name, sheet, ref)
MsgBox test
End Sub

Private Function GetValue(path, file, sheet, ref)
' Return a value from a closed workbook
Dim arg As String

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function

Function GetSourceData(fname, sname, cell) As String
' Return value from source file if exists, otherwise return error
Dim Sep As String
Dim ThisDocsFullName As String
Dim ThisDocsPath As String
Dim name, sheet, ref As String

GetSourceData = ""
Sep = Application.PathSeparator
ThisDocsFullName = Application.ActiveWorkbook.FullName
ThisDocsPath = Left(ThisDocsFullName, InStrRev(ThisDocsFullName,
Sep))
If FileExists(name) Then
GetSourceData = GetValue(ThisDocsPath, fname, sname, cell)
End If
End Function
---------------------------------------------------------------------------------------------

When I run the TestFileExists procedure everything works fine. It
returns the value from the spreadsheet. However, when I put the
function in a cell in the workbook like this:
=getsourcedata("managerstmtmo01.xls","managerstmtm o01","D10")
it returns a #VALUE! error. After debugging both the TestFileExists
procedure and the getsourcedata function it seems like there's a
problem with what's being returned from ExecuteExcel4Macro. When run
through the procedure I can display the value of GetValue and it's the
correct data. But when I run just the getsourcedata function I can't
see what the GetValue value is. I get an "Out of Context" message for
the value. Any idea what may be causing this problem?

Thanks.

I will read your previous post carefully. Here is the mail that I was
talking about

/Fredrik

"I recently found out how to read values from a sheet in another closed
workbook using the GetValue function I have pasted below. It builds a
string and then calls ExecuteExcel4Macro to return the value from the
other sheet. In my application, all the values in the sheet being read
are integers and I am trying to store the values I read as integers so
I can do math operations on them. However, if I try to cast the return
value as an integer I get a type mismatch error. (example: temp =
Val(GetValue(.....)) returns an error).

Can someone please advise me on how I can access these values as
integers.

Thanks!

This is a minor adaptation I based on John Walkenbach's GetValue()
function so that works cross-platform:


Public Function GetValue(Path, File, Sheet, Ref) As Variant
'Based on John Walkenbach's GetValue function:
'http://www.j-walk.com/ss/excel /tips/tip82.htm
Const sTEMPLATE As String = "'&P[&F]&S'!&R"
Dim sSEP As String
Dim sArg As String


sSEP = Application.PathSeparator
If Right(Path, 1) < sSEP Then Path = Path & sSEP
If Dir(Path & File) = "" Then
GetValue = "File Not Found"
Else
With Application
sArg = .Substitute(.Substitute(.Subst
itute(.Substitute( _
sTEMPLATE, "&R", Range(Ref).Address(True, True,
xlR1C1)), _
"&S", Sheet), "&F", File), "&P", Path)
End With
GetValue = ExecuteExcel4Macro(sArg)
End If
End Function


Note that it uses an XL4M command. It cannot be used from the
worksheet,
but works fine when called by a macro."

wrote in message
oups.com...
I actually started to do this after I posted this question yesterday. I
seem to be running up against another problem now. I have the following
procedures/functions in my workbook. I'm using ExecuteExcel4Macro
because I don't know if the source workbook will be open when this
workbook is open. Here's what I have:

--------------------------------------------------------------------------
-------------------
Option Explicit

Function FileExists(fname) As Boolean
' Return TRUE if the file exists in current directory
Dim Sep As String
Dim ThisDocsFullName As String
Dim ThisDocsPath As String
Dim SearchDocsFullName As String

On Error Resume Next

Sep = Application.PathSeparator
ThisDocsFullName = Application.ActiveWorkbook.FullName
ThisDocsPath = Left(ThisDocsFullName, InStrRev(ThisDocsFullName,
Sep))
SearchDocsFullName = ThisDocsPath & fname
FileExists = Dir(SearchDocsFullName) < ""
End Function

Sub TestFileExists()
Dim name As String
Dim sheet As String
Dim ref As String
Dim test As String

name = "managerstmtmo01.xls"
sheet = "managerstmtmo01"
ref = "D10"
test = GetSourceData(name, sheet, ref)
MsgBox test
End Sub

Private Function GetValue(path, file, sheet, ref)
' Return a value from a closed workbook
Dim arg As String

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function

Function GetSourceData(fname, sname, cell) As String
' Return value from source file if exists, otherwise return error
Dim Sep As String
Dim ThisDocsFullName As String
Dim ThisDocsPath As String
Dim name, sheet, ref As String

GetSourceData = ""
Sep = Application.PathSeparator
ThisDocsFullName = Application.ActiveWorkbook.FullName
ThisDocsPath = Left(ThisDocsFullName, InStrRev(ThisDocsFullName,
Sep))
If FileExists(name) Then
GetSourceData = GetValue(ThisDocsPath, fname, sname, cell)
End If
End Function
--------------------------------------------------------------------------
-------------------

When I run the TestFileExists procedure everything works fine. It
returns the value from the spreadsheet. However, when I put the
function in a cell in the workbook like this:
=getsourcedata("managerstmtmo01.xls","managerstmtm o01","D10")
it returns a #VALUE! error. After debugging both the TestFileExists
procedure and the getsourcedata function it seems like there's a
problem with what's being returned from ExecuteExcel4Macro. When run
through the procedure I can display the value of GetValue and it's the
correct data. But when I run just the getsourcedata function I can't
see what the GetValue value is. I get an "Out of Context" message for
the value. Any idea what may be causing this problem?

Thanks.


Your code is very differnt from what I should have written. First of all,
you should change this line:

Private Function GetValue(path, file, sheet, ref)

to something like this

Private Function GetValue(ByVal path As String , ByVal file As String ,
ByVal sheet As String , ref) As Variant

IO don't quite understand how you use ref. Is it a range?? What does the
code below do?
Range(ref).Range("A1").Address(, , xlR1C1)


In addition you should use an ErrorHandler

Private Function GetValue(...
On Error GoTo ErrHandler
'Your code here

GetValue = whatever
exit function

ErrHandler
GetValue = Err.Description
End Function

/Fredrik

Okay - I changed my GetValue function to read the way you suggested and
I get this error returned:

"Application-defined or object-defined error" when I execute the
GetSourceData function. But everything works fine when I run the
TestFileExists procedure that calls GetSourceData. The error is coming
from the ExecuteExcel4Macro(arg) line. Is there a reason this returns
an error when called from a function put in a cell and works when
called from a function that's called from a procedure?

Thanks.

wrote in message
oups.com...
Okay - I changed my GetValue function to read the way you suggested and
I get this error returned:

"Application-defined or object-defined error" when I execute the
GetSourceData function. But everything works fine when I run the
TestFileExists procedure that calls GetSourceData. The error is coming
from the ExecuteExcel4Macro(arg) line. Is there a reason this returns
an error when called from a function put in a cell and works when
called from a function that's called from a procedure?

Thanks.


None that I can think of right now. Can you show the value of arg when you
get the error?

/Fredrik

Here's my modified function:

Private Function GetValue(ByVal path As String, ByVal file As String,
ByVal sheet As String, ref) As Variant
' Return a value from a workbook
Const template As String = "'&P[&F]&S'!&R"
Dim arg As String
Dim sep As String

On Error GoTo ErrHandler

sep = Application.PathSeparator
If Right(path, 1) < sep Then path = path & sep
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Else
With Application
arg = .Substitute(.Substitute(.Substitute(.Substitute( _
template, "&R", Range(ref).Address(True, True,
xlR1C1)), _
"&S", sheet), "&F", file), "&P", path)
End With
GetValue = ExecuteExcel4Macro(arg)
End If
Exit Function

ErrHandler:
GetValue = Err.Description
End Function


And this is the value of arg when called from a procedu

"'C:\Documents and Settings\jlewis\My
Documents\Financials\[managerstmtmo01.xls]managerstmtmo01'!R10C4"

And this is the value of arg when called from a function within the
cell:

"'C:\Documents and Settings\jlewis\My
Documents\Financials\[managerstmtmo01.xls]managerstmtmo01'!R10C4"

Looks pretty much the same to me. In the documentation for
ExecuteExcel4Macro it says that the function cannot be used from the
worksheet (because it uses the XL4M command) but works fine when called
by a macro. Is there a problem calling it from another function that's
used from the worksheet?

Thanks.

wrote in message
oups.com...
Here's my modified function:

Private Function GetValue(ByVal path As String, ByVal file As String,
ByVal sheet As String, ref) As Variant
' Return a value from a workbook
Const template As String = "'&P[&F]&S'!&R"
Dim arg As String
Dim sep As String

On Error GoTo ErrHandler

sep = Application.PathSeparator
If Right(path, 1) < sep Then path = path & sep
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Else
With Application
arg = .Substitute(.Substitute(.Substitute(.Substitute( _
template, "&R", Range(ref).Address(True, True,
xlR1C1)), _
"&S", sheet), "&F", file), "&P", path)
End With
GetValue = ExecuteExcel4Macro(arg)
End If
Exit Function

ErrHandler:
GetValue = Err.Description
End Function


And this is the value of arg when called from a procedu

"'C:\Documents and Settings\jlewis\My
Documents\Financials\[managerstmtmo01.xls]managerstmtmo01'!R10C4"

And this is the value of arg when called from a function within the
cell:

"'C:\Documents and Settings\jlewis\My
Documents\Financials\[managerstmtmo01.xls]managerstmtmo01'!R10C4"

Looks pretty much the same to me. In the documentation for
ExecuteExcel4Macro it says that the function cannot be used from the
worksheet (because it uses the XL4M command) but works fine when called
by a macro. Is there a problem calling it from another function that's
used from the worksheet?

Thanks.


Some things can't be called from worksheets. I need to see if I can find
anything. The idea with this code is that the file doesn't have to be open,
right?

/Fredrik

I don't know if this sheds any light on any of this but the error I
told you about I got while debugging. When I try to run the function
without debugging I get this error returned:

Method 'ExecuteExcel4Macro' of object '_Global' failed

Not sure why the error is different depending on if I'm debugging or
just executing the function. Maybe this helps???

wrote in message
oups.com...
I don't know if this sheds any light on any of this but the error I
told you about I got while debugging. When I try to run the function
without debugging I get this error returned:

Method 'ExecuteExcel4Macro' of object '_Global' failed

Not sure why the error is different depending on if I'm debugging or
just executing the function. Maybe this helps???


Maybe. I shall try to make a small macro to do something similar. I have
made a macro for a friend where I use the Evaluate function instead. It
works well but the sheets have to be open for it to work. I now feel I have
some more time since I now have a new job. It's so nice not being
unemployed.

/Fredrik

Thanks - I appreciate all your help. As for the "being unemployed" I
understand. Been there, done that and I agree - not as nice as being
employed.

I haven't followed this thread too closely, but that UDF based on John
Walkenbach's code won't work if it's called by a formula in a worksheet.

But you may want to look at how Harlan Grove worked around using =indirect()
with closed workbooks.

http://www.google.com/groups?selm=sH...wsranger. com



Fredrik Wahlgren wrote:

wrote in message
oups.com...
I don't know if this sheds any light on any of this but the error I
told you about I got while debugging. When I try to run the function
without debugging I get this error returned:

Method 'ExecuteExcel4Macro' of object '_Global' failed

Not sure why the error is different depending on if I'm debugging or
just executing the function. Maybe this helps???


Maybe. I shall try to make a small macro to do something similar. I have
made a macro for a friend where I use the Evaluate function instead. It
works well but the sheets have to be open for it to work. I now feel I have
some more time since I now have a new job. It's so nice not being
unemployed.

/Fredrik

--

Dave Peterson

I thought this function you pointed me to would work but I'm still
having problems. I have my function set almost exactly as it is in the
post. I had to change the comparison to ErrRef to ErrValue and it
executed the rest of the code. Unfortunately when it came to the "For
Each c In r" the value being returned by xlapp.ExecuteExcel4Macro is
"empty" and I'm not getting my value.

This is what I have:

Function pull(xref As String) As Variant

Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, c As Range, n As Long, arg As String

pull = Evaluate(xref)

If CStr(pull) = CStr(CVErr(xlErrValue)) Then
On Error GoTo CleanUp

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add

On Error Resume Next

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)
Else
For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c
pull = r.Value
End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If
End Function


And I call the function in my cell with this:

=pull(""&F1&"\["&C1&"]"&D1&"'!D10")

where F1 references the path, c1 references the file and d1 references
the sheet name. Any reason this returns "empty"?

Thanks.

And as a follow-up this new function does work when I call it from a
procedure (as did the other function). Neither function works when
called from a cell in a workbook.

wrote in message
ups.com...
And as a follow-up this new function does work when I call it from a
procedure (as did the other function). Neither function works when
called from a cell in a workbook.

If the same string is evaluated by ExecuteExcel4Macro, that settles it. It
seems as if this function only works when called from a procedure

/Fredrik

That function works ok for me when called from a worksheet cell.

What did your formula look like?

" wrote:

I thought this function you pointed me to would work but I'm still
having problems. I have my function set almost exactly as it is in the
post. I had to change the comparison to ErrRef to ErrValue and it
executed the rest of the code. Unfortunately when it came to the "For
Each c In r" the value being returned by xlapp.ExecuteExcel4Macro is
"empty" and I'm not getting my value.

This is what I have:

Function pull(xref As String) As Variant

Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, c As Range, n As Long, arg As String

pull = Evaluate(xref)

If CStr(pull) = CStr(CVErr(xlErrValue)) Then
On Error GoTo CleanUp

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add

On Error Resume Next

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)
Else
For Each c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
Next c
pull = r.Value
End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If
End Function

And I call the function in my cell with this:

=pull(""&F1&"\["&C1&"]"&D1&"'!D10")

where F1 references the path, c1 references the file and d1 references
the sheet name. Any reason this returns "empty"?

Thanks.

--

Dave Peterson

"Dave Peterson" wrote in message
...
That function works ok for me when called from a worksheet cell.

What did your formula look like?

I have missed something. This has been a very long thread.

7Fredrik

I had suggested that Harlan Grove's =pull() UDF works from a cell on a
worksheet.
http://www.google.com/groups?selm=sH...wsranger. com

The OP said it didn't work for him.

Fredrik Wahlgren wrote:

"Dave Peterson" wrote in message
...
That function works ok for me when called from a worksheet cell.

What did your formula look like?

I have missed something. This has been a very long thread.

7Fredrik

--

Dave Peterson

Okay - I think I finally got it to work. It actually was something
simple - I had double quotation marks around the parameter being passed
to the pull function - which is wrong. It should be a single quotation
mark at the beginning around the path, file name and sheet name, ending
before the ! and cell reference. Once I took out the double quotation
marks it works fine. I misread it on the previous post.

I just have one other question - in Harlan Groves function he is
checking for the cell to have a #REF error in it before pulling the
value from another workbook. Any reason to do this? This was also
giving me some problems at first - sometimes I would have a #REF error
and sometimes I would have a #VALUE error. Any reason to have to check
for these errors at all?

Once agin, thanks to all who have helped me with this.

Harlan checks to see if the returned value is #ref. If it doesn't return that
error, then it means that the workbook is already open and you don't need his
function.

But even worse, I gave you a link to an older version (sorry).

http://groups.google.co.uk/groups?se...wsranger.co m

should work better.

" wrote:

Okay - I think I finally got it to work. It actually was something
simple - I had double quotation marks around the parameter being passed
to the pull function - which is wrong. It should be a single quotation
mark at the beginning around the path, file name and sheet name, ending
before the ! and cell reference. Once I took out the double quotation
marks it works fine. I misread it on the previous post.

I just have one other question - in Harlan Groves function he is
checking for the cell to have a #REF error in it before pulling the
value from another workbook. Any reason to do this? This was also
giving me some problems at first - sometimes I would have a #REF error
and sometimes I would have a #VALUE error. Any reason to have to check
for these errors at all?

Once agin, thanks to all who have helped me with this.

--

Dave Peterson