[RESOLVED] Updatelinks Only in One Worksheet

[Cristobal16]

Is there any way that I can update all of the links in one worksheet, while leaving the rest of the links in the other worksheets in my workbook alone? I have one worksheet with links that need to be updated regularly, and links in pivotcharts in several other worksheets that take FOREVER to update and don't need to be updated. All of the sheets need to stay in one workbook, so I can't split them into multiple excel files. As far as I can tell, .updatelinks only works with ActiveWorkbook, as in the following code (which is what I'm running right now):

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

Any ideas?

[Siddharth Rout]

.updatelinks only works with ActiveWorkbook

Yes that is the way Excel works....

In fact you got me thinking on how to achieve it... and to be honest I didn't have an answer... probably because there is no way "directly" to achieve what you want or probably I am not aware of one...

However, but there is one DIRTY way to get what you want... (I just thought of it )

1) Create a copy of your main workbook(Say Main.XLS) and save it as say Mainbkup.xls
2) Delete all worksheets except the one that you want to update in Mainbkup.xls
3) In this workbook, write a macro which will open you main workbook(Main.XLS) and
a) Delete the sheet that you want to update!!!!!!
b) Copy the worksheet from Mainbkup.xls to your main workbook after updating the links.

Hope this helps...

[Cristobal16]

Hey Koosid,

Thanks for the dirty workaround. As it turns out, when I tried that, it worked great, but it wasn't because of the LINKS in the other worksheets, it was actually because of the calculations the corresponded to pivot charts that I had made based on those links. When I updated the links in my workbook, all of the calculations that I had made based on those linked values were what was slowing me down so much. Basically, the workbook was recalculating once for every sequential link that got updated. So, I just disable calculation while updating the links, then reenabled calculation afterwards:

Dim xlCalc As XlCalculation



    xlCalc = Application.Calculation

    Application.Calculation = xlCalculationManual


ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

Application.Calculation = xlCalculationAutomatic

Thanks for the dirty solution - it was just what I needed to kick-start my brain again.

[Siddharth Rout]

Great. If your query is solved the do remember to mark the thread resolved. Check my signature on how to do it

[Cristobal16]

Oh, I remember. You've helped me a million times.