Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChangeLinks fails if formulae exceed 1024 character limit
Hi all
I have written some code that loops through the links in a workbook and updates them to different paths. To be more specific, it updates the paths from drive letters to UNC paths. Generally, this works, but fails where the new path forces formulae in the workbook to exceed Excel's 1024 character limit. Interestingly, the link update fails, but it raises no error, so there's not an automatic means of determining that one or more links has failed to update. I would like to know after the code has completed whether any updates have failed. Does anyone have a fix or workaround for this? Thanks in advance Paul Martin Melbourne, Australia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChangeLinks fails if formulae exceed 1024 character limit
I haven't run into it, so I don't have a tested workaround, but here is a
thought: did the LinkSources list change? if not, you could get the list again (after the change) and compare to the original list - if they haven't changed, you know you have a problem. Or compare the new list to see it the desired change is included. -- Regards, Tom Ogilvy "Paul Martin" wrote in message ups.com... Hi all I have written some code that loops through the links in a workbook and updates them to different paths. To be more specific, it updates the paths from drive letters to UNC paths. Generally, this works, but fails where the new path forces formulae in the workbook to exceed Excel's 1024 character limit. Interestingly, the link update fails, but it raises no error, so there's not an automatic means of determining that one or more links has failed to update. I would like to know after the code has completed whether any updates have failed. Does anyone have a fix or workaround for this? Thanks in advance Paul Martin Melbourne, Australia |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChangeLinks fails if formulae exceed 1024 character limit
Hi Tom
Yes, that sounds like a good solution. I'll give it a try. Thanks Paul Tom Ogilvy wrote: I haven't run into it, so I don't have a tested workaround, but here is a thought: did the LinkSources list change? if not, you could get the list again (after the change) and compare to the original list - if they haven't changed, you know you have a problem. Or compare the new list to see it the desired change is included. -- Regards, Tom Ogilvy "Paul Martin" wrote in message ups.com... Hi all I have written some code that loops through the links in a workbook and updates them to different paths. To be more specific, it updates the paths from drive letters to UNC paths. Generally, this works, but fails where the new path forces formulae in the workbook to exceed Excel's 1024 character limit. Interestingly, the link update fails, but it raises no error, so there's not an automatic means of determining that one or more links has failed to update. I would like to know after the code has completed whether any updates have failed. Does anyone have a fix or workaround for this? Thanks in advance Paul Martin Melbourne, Australia |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChangeLinks fails if formulae exceed 1024 character limit
For the benefit of anyone interested, the solution I used was:
astrTestLinks = Workbooks(intWbk).LinkSources(xlExcelLinks) blnLinkUpdated = True For i = 1 To UBound(astrLinks) If astrTestLinks(i) = strLink Then blnLinkUpdated = False Exit For End If Next i If blnLinkUpdated = True Then ' Success Else ' Failu updating link causes formulae to exceed XL's 1024 character limit End If An interesting observation during debugging was that the links collection is random in order so there was the need to loop through all links to see if the link that had been apparently updated still existed. Paul Paul Martin wrote: Hi Tom Yes, that sounds like a good solution. I'll give it a try. Thanks Paul Tom Ogilvy wrote: I haven't run into it, so I don't have a tested workaround, but here is a thought: did the LinkSources list change? if not, you could get the list again (after the change) and compare to the original list - if they haven't changed, you know you have a problem. Or compare the new list to see it the desired change is included. -- Regards, Tom Ogilvy "Paul Martin" wrote in message ups.com... Hi all I have written some code that loops through the links in a workbook and updates them to different paths. To be more specific, it updates the paths from drive letters to UNC paths. Generally, this works, but fails where the new path forces formulae in the workbook to exceed Excel's 1024 character limit. Interestingly, the link update fails, but it raises no error, so there's not an automatic means of determining that one or more links has failed to update. I would like to know after the code has completed whether any updates have failed. Does anyone have a fix or workaround for this? Thanks in advance Paul Martin Melbourne, Australia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel text export limit - 1024 per line (not cell), workaround? | Excel Discussion (Misc queries) | |||
Error message is Character Limit is over 1024 characters | Excel Discussion (Misc queries) | |||
Exceed Row Limit | Excel Worksheet Functions | |||
Beyond 1024 cell content limit | Excel Programming | |||
Help with text box limit (1024) | Excel Programming |