ChangeLink error

[Pez51]

I have this macro that worked last week now it does not? I am not sure what has changed

Run-time error '1004';
Method'ChangeLink' of object'_Workbook' failed

code:

Sub ChangeLink()
    
'Changes Link to Trial Balances
    
    Dim OldLink As String
    Dim NewLink As String
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Woodbury")
    OldLink = ws.Range("K1").Value
    NewLink = ws.Range("K2").Value
    ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks

Any ideas???

[nigelbloomy]

This could happen if the old link is not in the workbook.

You could run something like this to see what links are in the workbook:

Dim arrLinks
Dim i As Long
arrLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(arrLinks) Then
     On Error Resume Next
        For i = LBound(arrLinks) To UBound(arrLinks)
            Debug.Print arrLinks(i)
        Next i
End If

[Pez51]

Thank you for your input and advice. This code showed me that my oldlink was in there, and allowed the code to procees, but never changed the link.
still bombs on this line:
ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks

Is there are way to see which part of the code is the issue?

[nigelbloomy]

Can you post an example of your spreadsheet without any sensitive data? You can click on "Go Advanced" and then use the paperclip icon to attach a file.

As long as the old link exists I don't get that error. Even if the new link doesn't point to an actual file, it still runs and I get a #REF error in place of the old link.

[JBeaucaire]

At the point when it bombs and you DEBUG, hover your mouse over the OldLink and the NewLink and examine them carefully. Spot any errors?

    Dim OldLink As String
    Dim NewLink As String
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Woodbury")
    OldLink = ws.Range("K1").Value
    NewLink = ws.Range("K2").Value
    Debug.Print OldLink
    Debug.Print NewLink
    ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks

The two lines of code added above will PRINT those values into the Immediate Window (CTRL-G in the VBEditor to open that window), copy and paste those printouts here to the forum so we can examine them together.

[Pez51]

Thank you this helped me find the error. I had to do with the link address,
It should have been O:\IndexGroup\Equities\Cash Management....
but was going to
\\ad2.prod\wwl\himco-data\invdata\SHARE\IndexGroup\Equities\Cash Management...

I can't thank you enough for helping debug!!!

[JBeaucaire]

Glad to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[utkucansa]

I had the same problem as well. The debug thing worked really well. The reason of the problem can be date format. The one should be careful about that.

Thousand times thanks!

Regards

[TheSpatula]

Dont know about anyone else, I have had this problem occur for a user a few times.

Im an an admin not a coder and don't really understand what is going on i just know what is happening in our situation.

In the code the ChangeLink method is being run with the option Type:=xlLinkTypeExcelLinks

I've found that if the following registry key:
Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options\Options5 = 0x0000000
Then the code gives the run-time error of 1004

If i delete the key, after running the code the key goes to 0x00000080 and works fine.

No idea what the bit flag represents i found a document which seems to imply that it might have something to do with tooltips in older version of excel, but it seems odd that would cause an error with this method.

It happens to the user once or twice a year and in the past i've just cleared the excel settings, but i finally narrowed it down to this one registry key.

Hopefully this might helps someone else.

[carlosrgns]

i know this is an old thread but here is a working solution, it addresses a strange bug i found in Excel 2016
If the link is not referenced in the active sheet then excel goes bananas

Private Function UpdateXlsLinkSource(oldLinkPathAndFile As String, newLinkPathAndFile As String) As Boolean
UpdateXlsLinkSource = False

Dim lSources As Variant
lSources = ThisWorkbook.LinkSources(xlExcelLinks) 'array that contains all the links with path to excel files

Dim FILE_NAME As String
    FILE_NAME = Right(newLinkPathAndFile, Len(newLinkPathAndFile) - InStrRev(newLinkPathAndFile, "\"))  'name of the file without path

Dim theFileIsAlreadyOpen As Boolean
    theFileIsAlreadyOpen = file_open_module.IsWorkBookOpen(FILE_NAME)   'will check if the file is is open and return true or false

'check if a file with the same name is already open
If theFileIsAlreadyOpen Then
    newLinkPathAndFile = Workbooks(FILE_NAME).PATH & "\" & Workbooks(FILE_NAME).Name    'use the open file
Else
    Workbooks.Open FileName:=newLinkPathAndFile    'open the file if it wasn't already open
End If
theFileIsAlreadyOpen = True

'CHECK IF THE FILE NEEDS UPDATING
If newLinkPathAndFile = oldLinkPathAndFile Then
    UpdateXlsLinkSource = True  'if the link is unchanged update the values
    Exit Function
Else
    'step thru the existing links and see if it exists
    For Each Link In lSources
        If Link = oldLinkPathAndFile Then
            '''''''''''''''''''''''''''''''''''''
            For Each SHEET In ThisWorkbook.Worksheets   'this seemingly useless loop handles a bug where if a link is not referenced in the active sheet it crashes
                SHEET.Activate
                On Error Resume Next
                '''''''''''''''''''''''''''''''''''''
                ThisWorkbook.Activate
                ThisWorkbook.ChangeLink Name:=Link, NewName:=newLinkPathAndFile, Type:=xlExcelLinks  'update the link
                UpdateXlsLinkSource = True
                '''''''''''''''''''''''''''''''''''''
                If Err = 0 Then
                    On Error GoTo 0
                    Exit For
                End If
                Next SHEET
            '''''''''''''''''''''''''''''''''''''
            Exit For
        End If
    Next Link
    
    'check if the link was found AND WARN IF IT WAS NOT
    If Not UpdateXlsLinkSource Then
        MsgBox "Link to target not found"
        Exit Function
    End If
    
    If Not theFileIsAlreadyOpen Then    'CHECK IF THE FILE IS CLOSED, IF IT IS THEN OPEN IT
        Workbooks.Open (newLinkPathAndFile)
    End If
End If
End Function

[FDibbins]

Carl thanks for the update (I added code tags to your code, as per forum requirements )

[HKafeman]

carlosrgns, et al

I know your last post was from over 3 years ago now, but I hope you can help me with some clarification of what you found and your fix?

We have now started experiencing this problem with the latest Excel (e.g. for Microsoft 365 MSO (version 2401 Build 16.0.17231.20084) 64-bit, running on Windows 10).

This is with code that has been working okay for a long time!

The symptoms (strangely it does not happen every time performing the same actions with the same linked files!) are that when our code calls ThisWorkbook.ChangeLink:

Excel is completely stuck and cannot even be killed by Task Manager. The only way to continue is to restart Windows!

At this point another Excel window is opened showing the Home screen where you can select a New "Blank workbook", etc. You can close the additional Excel. BUT it is the original Excel window that is completely stuck!

Also "Svchost . exe (DcomLaunch -p)" is stuck taking about 27% of my CPU (which has 14 Physical and 20 Logical Cores), with this detail shown in Task Manager:

Service Host: DCOM Server Process Launcher (5)

System Events Broker
Power
Plug and Play
DCOM Server Process Launcher
Background Tasks and Infrastructure Service

The "Services and Controller App" is taking ~9% and Excel about ~6%!

Can you please explain further your solution?

Your comment says Excel goes wrong "If the link is not referenced in the active sheet".

So do you mean that you need to make sure that at least one cell in each sheet references each external Link?

If I understand your code correctly, it opens the new linked file, then finds the old link and sets up a loop through all the sheets in the Workbook.

However, as soon as it manages to activate the sheet and then activate the Workbook and call ChangeLink and set UpdateXlsLinkSource to True with Err = 0, it will exit the SHEET loop!

So I do not see how that prevents ChangeLink from hanging Excel and also Err will surely always be 0 from the "UpdateXlsLinkSource = True" line before it is tested????

Any clarification or pointers anyone can give on this will be much appreciated.

FYI
One of my colleagues thought the issue might be being caused by UDFs (recently added) using Named Ranges internally, rather than being passed all values they need to work. However, I think I have eliminated this as a possible cause.

He has said: "Unhandled errors in UDFs are ignored (the do not trigger run-time errors) and may cause Excel to get confused about the chain of calculations it needs to do.".

He has also seen Run time Error 10 "This array is fixed or temporarily locked" which he thinks also might be related to UDFs and/or RedDim statements!?! - However "Debug" then highlights a line which is 2 call stack levels above a call to ChangeLink with no ReDim statements anywhere in that code!?! - All very confusing.

Thanks
Henry

[AliGW]

Administrative Note:

We are happy to help, however whilst you feel your request is similar or even the same to this thread, we have a rule that you open your own thread on the issue and do not piggy back another member's thread.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread