xl2000 slow link update

mtbiker5150

New Member
Joined
Nov 8, 2005
Messages
7
I have a workbook that has vlookups which link to a file on a network drive. When trying to update links it takes a long time (about 3 minutes). A colleague who runs xl2003 can open the same file and the links update instantly. Any suggestions? My settings are:

ask to update links checked
Tools-->options-->calculation - set to automatic
xl2000 SR1

thanks
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Do you have the file open when trying to update the links or is it closed?

If it's closed you can try this code

Here is the Code add it to the thisworkbook in the VBA editor

<code>
Private Sub Workbook_Open()
ActiveSheet.Unprotect Password:="XXXX" Only if you have a password on the worksheet

Workbooks.Open Filename:= _
"Your network like here IE \\server\folder\excel.exe
"
Workbooks("your workbook name here").Activate
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources


Workbooks.Close Filename:= _
"Your network like here IE \\server\folder\excel.exe
Workbooks("your workbook name here").Activate


ActiveSheet.Protect Password:="XXXX", DrawingObjects:=True, Contents:=True, Scenarios:=True Only if you have a password here
End Sub
</code>

Let me know if you have any ? about it
 
Upvote 0
Thanks alot! It works now. In the workbooks.close command I didn't need to enter the path of the filename again.

When I open the file I have it ask me if I want to update links. I say no and it continues running your code. How can I have it not prompt me to update links? I know how to turn the prompt off, but if I do that then I run into the original problem of slow update of links.

thanks
 
Upvote 0
I'm not really sure about that one Maybe some one on the borad can come up with something I use this code on a liked network file with vlookups cuz i had the same issue as you I always just hit no


any one out there can help us with this one?

thanks
 
Upvote 0

Forum statistics

Threads
1,224,351
Messages
6,178,058
Members
452,822
Latest member
MtC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top