xl2000 slow link update

[mtbiker5150]

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

 

[MDuff]

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

 

[mtbiker5150]

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

 

[MDuff]

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