Prevent automatic updating of links to external workbooks

Posted by Artem on April 16, 2001 1:49 PM

Hello,

I have XL 97.

In my workbook I have links to cells in several other workbooks. Whenever I change a link I DON'T want Excel to automatically update it. Basically, I just want it to update my link if AND ONLY IF I do:
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

"Manual" Option in Edit > Links menu is always dimmed, no matter if I check or uncheck Options > Calculations > Update Remote Refernces, no matter what other settings are. I have read a couple of similar postings where XL Pros said those links are always automatic and nothing can be done about that, but I refuse to believe there's no solution. Maybe a VBA workaround? Thanks for any help!

Posted by Dave Hawley on April 16, 2001 2:02 PM

Hi Artem

If you are opening the Workbook via VBA then you can use the UpDateLinks argument:

Workbooks.Open "C:\MyDocuments\Book1.xls", UpDateLinks:=0

0= Doesn't update any references

1= Updates external references but not remote references.

2= Updates remote references but not external references.

3= Updates both remote and external references

Dave

OzGrid Business Applications

Posted by Artem on April 16, 2001 2:52 PM

Hi Dave,

Can it be done if the workbook is opened normally? maybe with Workbook_Open or Auto_Open events?

Thanks!

Posted by Dave Hawley on April 16, 2001 5:54 PM

Artem, the Manual option in the Links dialog box only changes the Update setting for the following objects linked into the workbook:

Embedded objects (such as a Microsoft Word document that is linked into a workbook)

OLE objects

Dynamic Data Exchange (DDE) objects

You cannot use the Workbook_Open fo this as the links will update BEFORE it fires. The only way I know of is the one I showed you.

Maybe this tip from David Hager will help you.

So, you could create a new very small workbook, whose job is to open the
main workbook, containing just:

Sub Auto_Open()
Workbooks.Open ThisWorkbook.Path & "\RealOne.XLS", UpdateLinks:=0
ThisWorkbook.Close False
End Sub

Dave

OzGrid Business Applications

Posted by Artem on April 16, 2001 6:18 PM

Thanks!

Thanks for help, Dave!

Dave