Updating master workbook from source that may/may not exist

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