UpdateLink giving error 1004

[DRK]

I've tried the below code in ThisWorkbook in both Workbook_Activate() and
Workbook_Open()

ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources
or
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

Both throw a run-time error '1004'. Message is 'UpdateLink' of object
'_Workbook' failed.

The other workbook is not open yet. Is this the cause of the problem? If so
what can I do about it? Do I need to open them simultaneously? If so, how can
I do that?

 

[Dave Peterson]

The purpose of updating links is so you don't have to open those other
workbooks.

If you run the code manually (just stepping through it with F8's), does it work
ok?

If it blows up there, I don't think it would be a timing issue.

 

[DRK]

No - stepping through doesn't make a difference. I'm using Excel 97 but I
doubt that that is the issue. Any other way to tell this workbook to update
its links upon opening?

 

[Dave Peterson]

There's an option that you can change:
tools|options|edit tab|ask to update automatic links

But that setting is used to determine if you want to be asked.

If you turn it off, then the links should be updated automatically.

If you turn it on, then it becomes your choice.
(note that this is not a workbook setting--it would have to be changed on each
pc)

But those links should update when the workbook opens (if you answered yes to
the prompt or turned the prompt off).

==
I should have answered this way the first time.

I guess my question becomes, "Are your links not updating when you open the
workbook?"

No - stepping through doesn't make a difference. I'm using Excel 97 but I
doubt that that is the issue. Any other way to tell this workbook to update
its links upon opening?

 

[DRK]

I've opted to uncheck the 'sk to update' box. The end users will be advised
to do the same or live with hitting that enter key to permit the update.

 

[Dave Peterson]

Ahhh. Now I see that you were trying to force the users to update links.

Another option is to give them a dummy workbook that opens your real workbook
with links updated the way you want.

The dummy workbook would contain a macro like this:

Kind of like:

Option Explicit
Sub auto_open()
Workbooks.Open Filename:="c:\my documents\excel\book2.xls", UpdateLinks:=1
ThisWorkbook.Close savechanges:=False
End Sub

Then you open the dummy workbook and the links will be refreshed.
(read about that UpdateLinks argument in VBA's help.)

But now you have a different problem--you have to make sure that the user opens
the file with macros enabled.

I add some notes to Sheet1:

Please close this workbook with macros enabled.
(Repeated about 20 times)

Then if macros are enabled, the message disappears quickly.

If macros are disabled, then the users can read the message and do what they
want/need to do.

I've opted to uncheck the 'sk to update' box. The end users will be advised
to do the same or live with hitting that enter key to permit the update.