Links created when files are copied and pasted

[timorrill]

It is common practice in my office to create Excel spreadsheets using functions from a custom add-in. Every user has a copy of the add-in on their computer. We often encounter the problem where links are introduced into the Excel worksheet, so that the user is prompted to Update Links whenever the file is opened.

Here is how I've recreated the problem. Let's say I create a spreadsheet on my hard drive using a function from the custom add-in. The formula that I enter is

=MyFormulaFromAddin(A5)

I save the workbook and close it. Then I copy the workbook to a network drive so that my colleagues can access it. Whenever they (or I) open the workbook from the network drive, there is a prompt to update links, and the formula reads

='C:\Documents and Settings\user-name\Application Data\Microsoft\Addins\CompanyAddin.xla'!MyFormulaFromAddin(A5)

Where user-name is my network login name.

How can I prevent this from happening? All of the users who have use this worksheet also have the add-in installed, so I would think that this shouldn't be occurring.

 

[timorrill]

Also, I should point out that I also get this error when the workbook was created on the network drive and then accessed by another computer.

What's going on? How do I fix it?

 

[timorrill]

Anyone?

 

[whitesabbathica9]

This is an old thread, but just for reference as it took me a while to figure this out ...

I am assuming you have some named ranges in your workbook. Whenever you copy a worksheet with named ranges Excel creates links between the workbooks.

I often copy worksheets between workbooks and ran into this problem. What I do is delete all the named ranges with a macro in my PERSONAL.XLS workbook ..

Public Sub DeleteAll_Names()
'delete all names in workbook in prepartion to copy sheet to another workbook
' (to prevent the 'hidden links' problem)
'
'   NOTE: be sure NOT to save the source workbook after you delete the names from it!

    Dim nm As Name
    
    Application.Calculation = xlCalculationManual
    For Each nm In ActiveWorkbook.Names
        nm.Delete
    Next
'*
End Sub

I then copy the worksheet with formulas intact. But be sure to test it out before distributing to make sure named ranges that referenced the copied sheet are adjusted properly.
Hope this helps someone.

-- removed inline image ---