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