Links to other spreadsheets

[Guest]

I am receiving a workbook via email that includes links to files I cannot
access. In Excel 2003, this was not a problem when i open the workbook,,, I
simply could not update the links. Now under 2007, it replaces the fields
with #NAME?. I can open a blank workbook and set recalc to manual then open
the intended file but then would not be able to do any manual recalc unles I
value copy all the externally linked cells.

 

[macropod]

Hi,

Did you try checking the 'Ask to update automatic links' option?

Cheers

 

[Guest]

Yes; that box is checked. When I try to open the file when it is first sent
to me, I never get a chance to answer the question whether to update links...
I immediately get a dialog box that says "One or more invalid names were
detected in this workbook. Those invalid names have been changed to #REF."

Hi,

Did you try checking the 'Ask to update automatic links' option?

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I am receiving a workbook via email that includes links to files I cannot
access. In Excel 2003, this was not a problem when i open the workbook,,, I
simply could not update the links. Now under 2007, it replaces the fields
with #NAME?. I can open a blank workbook and set recalc to manual then open
the intended file but then would not be able to do any manual recalc unles I
value copy all the externally linked cells.

 

[macropod]

Hi,

That sounds more like there is a problem with the names used in the workbook, rather than with any links to/from it. Maybe it's
corrupt. I'd suggest contacting the author to sort out the problem.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Yes; that box is checked. When I try to open the file when it is first sent
to me, I never get a chance to answer the question whether to update links...
I immediately get a dialog box that says "One or more invalid names were
detected in this workbook. Those invalid names have been changed to #REF."

Hi,

Did you try checking the 'Ask to update automatic links' option?

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I am receiving a workbook via email that includes links to files I cannot
access. In Excel 2003, this was not a problem when i open the workbook,,, I
simply could not update the links. Now under 2007, it replaces the fields
with #NAME?. I can open a blank workbook and set recalc to manual then open
the intended file but then would not be able to do any manual recalc unles I
value copy all the externally linked cells.

 

[Guest]

Thank for responding. I have a number of worksheets sent (emailed) from the
same source. I can open them with no trouble in 2003 but have the issue when
trying to open in 2007. I am assuming it is related to tightened security in
2007... unless the names were legal in 2003 and not in 2007.
Thanks again.

Hi,

That sounds more like there is a problem with the names used in the workbook, rather than with any links to/from it. Maybe it's
corrupt. I'd suggest contacting the author to sort out the problem.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Yes; that box is checked. When I try to open the file when it is first sent
to me, I never get a chance to answer the question whether to update links...
I immediately get a dialog box that says "One or more invalid names were
detected in this workbook. Those invalid names have been changed to #REF."

Hi,

Did you try checking the 'Ask to update automatic links' option?

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I am receiving a workbook via email that includes links to files I cannot
access. In Excel 2003, this was not a problem when i open the workbook,,, I
simply could not update the links. Now under 2007, it replaces the fields
with #NAME?. I can open a blank workbook and set recalc to manual then open
the intended file but then would not be able to do any manual recalc unles I
value copy all the externally linked cells.

 

[pistolpete66]

Unfortunately, you're dealing with what I consider a significant flaw in
Excel 2007. This link explains more, but essentially, any spreadsheets
created in a version prior to 2007 with links to other sheets or workbooks
external to the existing workbook will be recalculated and may/will return
errors. There's no way in Excel 2007 to prevent the auto-recalc, according
to the article. I've had the same issue come up and there's no real "fix",
just an ugly workaround. See this link for more info:

http://support.microsoft.com/kb/925893

Thank for responding. I have a number of worksheets sent (emailed) from the
same source. I can open them with no trouble in 2003 but have the issue when
trying to open in 2007. I am assuming it is related to tightened security in
2007... unless the names were legal in 2003 and not in 2007.
Thanks again.

Hi,

[quoted text clipped - 18 lines]

 

[macropod]

Ah, so the workbooks you're getting are in an older version? In that case, it's not just that you're using Excel 2007. Rather, this
is a long-standing practice in Excel, whereby automatic links in older workbook formats are recalculated regardless of the link
update setting. This behaviour goes back to at least Excel 2000.

As you've found, opening the files in the earlier version stops the update. Similarly, if your source changes over the Excel 2007
and saves the file in the new format, the problem will go away.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Thank for responding. I have a number of worksheets sent (emailed) from the
same source. I can open them with no trouble in 2003 but have the issue when
trying to open in 2007. I am assuming it is related to tightened security in
2007... unless the names were legal in 2003 and not in 2007.
Thanks again.

Hi,

That sounds more like there is a problem with the names used in the workbook, rather than with any links to/from it. Maybe it's
corrupt. I'd suggest contacting the author to sort out the problem.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Yes; that box is checked. When I try to open the file when it is first sent
to me, I never get a chance to answer the question whether to update links...
I immediately get a dialog box that says "One or more invalid names were
detected in this workbook. Those invalid names have been changed to #REF."

:

Hi,

Did you try checking the 'Ask to update automatic links' option?

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I am receiving a workbook via email that includes links to files I cannot
access. In Excel 2003, this was not a problem when i open the workbook,,, I
simply could not update the links. Now under 2007, it replaces the fields
with #NAME?. I can open a blank workbook and set recalc to manual then open
the intended file but then would not be able to do any manual recalc unles I
value copy all the externally linked cells.

 

[Compatibility link Excel 2007]

pistolpete66, Thank you for the explanation. It was right on and will keep
me from searching for a solution other than the workarounds in the link. I
have not checked this sight since you answered in mid-Nov. Thanks again.

Unfortunately, you're dealing with what I consider a significant flaw in
Excel 2007. This link explains more, but essentially, any spreadsheets
created in a version prior to 2007 with links to other sheets or workbooks
external to the existing workbook will be recalculated and may/will return
errors. There's no way in Excel 2007 to prevent the auto-recalc, according
to the article. I've had the same issue come up and there's no real "fix",
just an ugly workaround. See this link for more info:

http://support.microsoft.com/kb/925893

Thank for responding. I have a number of worksheets sent (emailed) from the
same source. I can open them with no trouble in 2003 but have the issue when
trying to open in 2007. I am assuming it is related to tightened security in
2007... unless the names were legal in 2003 and not in 2007.
Thanks again.

Hi,

[quoted text clipped - 18 lines]

the intended file but then would not be able to do any manual recalc unles I
value copy all the externally linked cells.

 

[Compatibility link Excel 2007]

Thanks macropod... I didn't mention that I was opening files created from an
earlier version - obviously, that was significant. Appreciate your help.

Ah, so the workbooks you're getting are in an older version? In that case, it's not just that you're using Excel 2007. Rather, this
is a long-standing practice in Excel, whereby automatic links in older workbook formats are recalculated regardless of the link
update setting. This behaviour goes back to at least Excel 2000.

As you've found, opening the files in the earlier version stops the update. Similarly, if your source changes over the Excel 2007
and saves the file in the new format, the problem will go away.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Thank for responding. I have a number of worksheets sent (emailed) from the
same source. I can open them with no trouble in 2003 but have the issue when
trying to open in 2007. I am assuming it is related to tightened security in
2007... unless the names were legal in 2003 and not in 2007.
Thanks again.

Hi,

That sounds more like there is a problem with the names used in the workbook, rather than with any links to/from it. Maybe it's
corrupt. I'd suggest contacting the author to sort out the problem.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Yes; that box is checked. When I try to open the file when it is first sent
to me, I never get a chance to answer the question whether to update links...
I immediately get a dialog box that says "One or more invalid names were
detected in this workbook. Those invalid names have been changed to #REF."

:

Hi,

Did you try checking the 'Ask to update automatic links' option?

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I am receiving a workbook via email that includes links to files I cannot
access. In Excel 2003, this was not a problem when i open the workbook,,, I
simply could not update the links. Now under 2007, it replaces the fields
with #NAME?. I can open a blank workbook and set recalc to manual then open
the intended file but then would not be able to do any manual recalc unles I
value copy all the externally linked cells.