ChangeLink error

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?

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!!!

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

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, 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