Reproducible Bug in Excel X with Changing Links that are used with specified sheet name

[jaques]

Yesterday, I was working on my fiscal year end (FYE). My books are kept
in Excel. In various extract sheets, I have to change links from the
last FYE GL to the new FYE GL. I made a VBA macro which used the
Workbook method 'changelinks'.

Inexplicably, with perfectly good path names, it would declare that the
external reference was invalid.
I finally found a REPRODUCIBLE ERROR. If I put single quotes inside the
double quotes, then it will bring up a dialog to ask where the file is.
If I cancel that, then it will work on the next attempt.

I tried relaunching XL. No luck. I tried using the ChDir command just
before the changelinks command. It worked once, but not again. It's
so weird! The other link changing stuff I do works fine! I even tried a
dummy file to link to, in case the presence of multiple worksheets in
the new target was breaking it.

That led to the answer. Even though the old links only show the file in
the link dialog, they were being used in connection with a specific
sheet name, which didn't exist in the new target. If I did a S&R, I
cld change it. However, a partial S&R, of just the sheet name, so I cld
then change the link, would just grind for a while then do nothing. You
must S&R for both the file and the sheet concurrently.

Actually, trying to record the macro led to the answer. I went through
the normal steps, but then got the stupid error message that the
external reference was invalid.

Again, I found no spot on the MS site for bug reports, so I post here,
in the hopes it is monitored and that Excel X is being supported.

 

[JE McGimpsey]

Inexplicably, with perfectly good path names, it would declare that the
external reference was invalid.
I finally found a REPRODUCIBLE ERROR. If I put single quotes inside the
double quotes, then it will bring up a dialog to ask where the file is.
If I cancel that, then it will work on the next attempt.

Could you post a precise procedure, including path and filenames, for
which you can reproduce this?

 

[jaques]

Thank you for responding.

I have published 3 files to demonstrate this error, at
ftp://hierlawhierlaw.com/XL Bug Re Linked Sheets.zip. However, as I
worked on it, I found it wasn't quite as bad as I thought initially.

The problem is that neither Excel's Links dialog nor its Workbook
changelinks method can handle a change to a link to a specified sheet,
where you are changing both the sheetname and the file. Here is the
procedure:

*Open all 3 files.
*Activate Linker
*Observe that 'old data' is a reference to the 'target' file.
*Hit the 'ChangeLinks' button.
*Observe that 'old data' has become a reference to the 'newTarget'
file, and now reads 'new data'.
*Hit the 'change links back' button.
*Observe the old reference has been restored.
*Hit the 'Search & Replace Links' button.
*Observe that the macro I wrote to do so works. The reason for this
macro is to not fail if the old link doesn't exist, but rather to
simply add a link. [that explains one of the curiosities, I now
realize].
*Now change the name of the sheet name in the 'newTarget' file.
*Try the 'changeLinks' button'. It fails.
*Try the 'search & replace Links' button. It fails.
*Try doing it manually, with the 'links' dialog under the edit menu.
*Observe that it doesn't offer any assistance about finding the
appropriate sheet.(not exactly a bug, but certainly a flaw).
*Now try the 'changeLinks with single quotes' button.
*Observe that a file dialog is brought up.
*Hit Cancel.
*Observe that a change was made nevertheless (a bug).
*Now try the 'search & replace links' button. It appears to work but
the link is invalid (not exactly a bug, but certainly a flaw).
*Now manually restore the link to the old file.
*Try a manual 'search and replace', just changing the sheet name, in
anticipation of then changing the link.
*Observe that a dialog comes up, offering a choice of sheets, the new
non-existent one, and the old valid one.
*Hit cancel.
*Observe that a change was made nevertheless (a bug).
*Do another manual 'search and replace', this time using an exclamation
point as a suffix to ensure that only the sheet is changed, e.g.,
"targetSheet!" to "newTargetSheet!"
*Observe that it merely complains of an invalid external reference.
That isn't helpful or consistent but it isn't a bug.

Please send a copy of any replies to my email address.