I am working with a workbook that has several links to other workbooks, but I noticed that if I use the Workbook.ChangeLink() method, it takes too long to finish. However, if I manually use the Excel interface with the functionality to change the source of a link, and at the same time have that new source, which is an open Excel workbook, then i click to the Design > Edit Links > Change Source... functionality, which is equivalent to Workbook.ChangeLink(), takes very little time to complete.
I have tried opening the new source with excel.interop in a new app, but it doesn't have the same time effect as doing it manually from the Excel App interface.
Do you know if there is any alternative to update my SourceLinks more quickly?
Thank you in advance.
what i did
ExternalWb = ExternalExcelApp.Workbooks.Open(Filename:=newLinkString, Password:=in_GeneralPassword, ReadOnly:=False, WriteResPassword:=in_GeneralPassword)
Threading.Thread.Sleep(8000)
Wb.ChangeLink(linkString, newLinkString, Microsoft.Office.Interop.Excel.XlLinkType.xlLinkTypeExcelLinks)
expected speed: about 30secs.
real speed: about 15mins.
using excel app interface (with newLinkString workbook open in other Excel App interface)
real speed: about 30secs.