Links created when files are copied and pasted

timorrill

Well-known Member
Joined
Sep 20, 2006
Messages
528
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

Code:
=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

Code:
='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.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

timorrill

Well-known Member
Joined
Sep 20, 2006
Messages
528
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?
 

whitesabbathica9

New Member
Joined
Jun 3, 2008
Messages
47
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 ..
Code:
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 ---
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top