B
brewman_63
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.
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.