Closed Thread
Results 1 to 13 of 13

ChangeLink error

  1. #1
    Registered User
    Join Date
    07-14-2015
    Location
    Hartford
    MS-Off Ver
    Professional Plus 2010
    Posts
    3

    ChangeLink error

    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:
    Please Login or Register  to view this content.
    Any ideas???
    Last edited by JBeaucaire; 07-14-2015 at 04:41 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.

  2. #2
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: ChangeLink error

    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:
    Please Login or Register  to view this content.
    Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.

    <---Click * Add Reputation for all helpful comments. It's like giving a smile.
    Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.

  3. #3
    Registered User
    Join Date
    07-14-2015
    Location
    Hartford
    MS-Off Ver
    Professional Plus 2010
    Posts
    3

    Re: 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?

  4. #4
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: ChangeLink error

    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.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: ChangeLink error

    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?

    Please Login or Register  to view this content.
    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.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  6. #6
    Registered User
    Join Date
    07-14-2015
    Location
    Hartford
    MS-Off Ver
    Professional Plus 2010
    Posts
    3

    Re: ChangeLink error

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

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: ChangeLink error

    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.

  8. #8
    Registered User
    Join Date
    01-09-2020
    Location
    Aachen, Germany
    MS-Off Ver
    2013
    Posts
    1

    Re: ChangeLink error

    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

  9. #9
    Registered User
    Join Date
    09-10-2020
    Location
    Sydney
    MS-Off Ver
    2016
    Posts
    1

    Re: ChangeLink error

    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.

  10. #10
    Registered User
    Join Date
    11-17-2020
    Location
    Buenos Aires
    MS-Off Ver
    2016
    Posts
    1

    Re: ChangeLink error

    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

    Please Login or Register  to view this content.
    Last edited by FDibbins; 11-18-2020 at 01:48 AM.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: ChangeLink error

    Carl thanks for the update (I added code tags to your code, as per forum requirements )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  12. #12
    Registered User
    Join Date
    10-16-2020
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: ChangeLink error

    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

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: ChangeLink error

    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
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 08-16-2013, 12:04 PM
  2. [SOLVED] ChangeLink Command Not Working
    By Dina in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-23-2012, 03:57 PM
  3. ChangeLink with variable new name
    By DeeCeeL in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2012, 01:58 PM
  4. ChangeLink Name Issues
    By alex_spun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-29-2011, 03:11 PM
  5. ChangeLink with remote file
    By JackL in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-11-2008, 10:51 AM
  6. Replies: 1
    Last Post: 04-15-2006, 03:03 AM
  7. [SOLVED] ChangeLink - What's wrong with the following?
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-13-2006, 11:20 AM
  8. changelink with the name and new name as strings is failing
    By whylite in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2006, 01:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1