An Excel workbook that is one of several in a application that I have written is having one of its Edit Links unexpectedly changed. I am seeking help as to why. The application has VBA code manipulating files and cells galore. My suspicions are pointing me to some unexpected (by me) behavior in the SaveAs method. My basic question is: could a SaveAs method cause an Edit Link setting to change in a “distantly related” workbook? The VBA code with the SaveAs is running in a separate workbook, not otherwise mentioned below. Here are details you might need in order to offer an opinion:

I have a workbook that is my “database” of names. It is represented by the WorkbookObject “MyNamesFile” . As part of the application logic, the macro is designed to create a copy of this Names workbook (so that it can manipulate it without upsetting the remaining elements in the overall application). The code below does the SaveAs, storing the new workbook, called “Mailing List Wok Area” in the same folder as the original Names workbook represented by MyNamesFile. See the code:


See the code:
Please Login or Register  to view this content.
So, at this point the WorkbookObject, MyNamesFile,represents the new workbook with a file name of “Mailing List Work Area”. All this seems to be working properly.

Later lines of code re-open the original Names workbook using a different WorkbookObject to represent it in the code. Neither the original Names workbook nor the new “Mailing List Work Area” workbook get saved. On disk, the "Mailing List Work Area" workbook, as created with the SaveAs method, remains as it was created. The active, processed (sorted, and otherwise manipulated) version of this workbook get closed without saving.

Hang with me: There is a third workbook, not opened as part of this processing, where the problem exists. This third workbook is Edit Link-ed to the original Names workbook.

The problem I am finding is that when I later open this third workbook, the Edit Link there has been mysteriously (to me) changed to point to the "Mailing List Work Area" workbook instead of the original Names workbook.

Frankly, I don’t know if my suspicion of the SaveAs as the culprit is correct. Does anyone have an explanation? Is there some behavior of SaveAs that is changing this workbook? Could it be happening when the third workbook is opened, not when the SaveAs is executed? Or, are there other unrelated behaviors that I can control. I have not seen this behavior on my development machine, but (Murphy’s Law) it happened on the production machine!



Any help would be appreciated.