Writing a macro to change external links to manual updating in Excel 2000

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Wirt - 14 Feb 2004 14:30 GMT

Can a VBA routine be written to change external links to manual updating
after they have been initially created in a workbook and that workbook has
been closed?

I would like to write a VBA procedure that can be included in a workbook
template so that external links created in any workbooks created from the
template can be manually changed from manual to autmatic updating.

Is this possible?

John Wirt

Reply to this Message

Tom Ogilvy - 14 Feb 2004 14:42 GMT

Excel likes to other excel workbooks are always automatic.

Beyond that, if you can do it manually, turn on the macro recorder and you
have your code.

--
Regards,
Tom Ogilvy

> Can a VBA routine be written to change external links to manual updating
> after they have been initially created in a workbook and that workbook has
[quoted text clipped - 7 lines]
>
> John Wirt

Reply to this Message

John Wirt - 15 Feb 2004 20:59 GMT

Unfrotunately, no, I do not think that link updating can be turned off using
any available menu selections.

John

> Excel likes to other excel workbooks are always automatic.
>
> Beyond that, if you can do it manually, turn on the macro recorder and you
> have your code.

Reply to this Message

Tom Ogilvy - 15 Feb 2004 21:11 GMT

What can't be done manually generally can't be done with code when it comes
to settings and so forth. reference changing a link from automatic to
manual - manual grayed out in the menu, can't be done.

Signature

Regards,
Tom Ogilvy

> Unfrotunately, no, I do not think that link updating can be turned off using
> any available menu selections.
[quoted text clipped - 5 lines]
> > Beyond that, if you can do it manually, turn on the macro recorder and you
> > have your code.

Reply to this Message

John Wirt - 15 Feb 2004 21:06 GMT

In the ..excel.links group, Bill Manville MVP - Microsoft Excel, Oxford,
England, says that there is an Excel 4 command that works:

- - - - - - - - -
VBA does not have a method of its own to change the update method of a
link.

However, you can use a VBA statement that executes an Excel 4 macro that
will do the job for you while the workbook is open (I discovered this by
recording a macro while changing a link's update method)

V = ThisWorkbook.LinkSources(xlOLELinks)
' change to manual updating
ExecuteExcel4Macro "SET.UPDATE.STATUS(""" & V(1) & """,2,2)"
- - - - - -
However, this macro will not run as a VBA procedure, no matter how I try to
define V as a variable.

What are Excel4Macros?

John Wirt
- - - - -

> Excel likes to other excel workbooks are always automatic.
>
[quoted text clipped - 4 lines]
> Regards,
> Tom Ogilvy

Reply to this Message

Tom Ogilvy - 15 Feb 2004 21:26 GMT

That isn't really what he said. He said he recorded a macro when he did it
manually.

You can only manually change an OLE link to manual - so good chance that you
are not using OLE links (based on your post in links). therefore passing it
a link source that can't be changed would raise an error in any version of
VBA/Excel or even the native Macro 4 macro language.

Also, macros don't fire before the prompt so your concept of doing it on
opening is flawed as well.

I suggested a method you could use in Links.

Signature

Regards,
Tom Ogilvy

> In the ..excel.links group, Bill Manville MVP - Microsoft Excel, Oxford,
> England, says that there is an Excel 4 command that works:
[quoted text clipped - 26 lines]
> > Regards,
> > Tom Ogilvy

Reply to this Message

Bill Manville - 16 Feb 2004 08:03 GMT

> In the ..excel.links group, Bill Manville MVP - Microsoft Excel, Oxford,
> England, says that there is an Excel 4 command that works:

I assumed you were talking about remote links (to non-Excel files) rather
than external links (to cells in other workbooks). As Tom has said,
external links are always automatic.

I also agree with Tom in that the only solutions open to you are:
- Excel 2002 or later which allows you to specify that the Update Links
question be not asked at startup.
- In Excel 2000 you could replace formulas containing the links with
values (I would suggest doing this when first saving the workbook rather
than on the first open - at which time you can't prevent the Update Links
question)
- Use another workbook to open the real workbook, specifying
UpdateLinks:=0

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message