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

  • Thread starter Thread starter John Wirt
  • Start date Start date
J

John Wirt

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
 
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.
 
Unfrotunately, no, I do not think that link updating can be turned off using
any available menu selections.

John
 
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
- - - - -
 
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.
 
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.

--
Regards,
Tom Ogilvy

John Wirt said:
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
- - - - -
Click to expand...
 
John said:
In the ..excel.links group, Bill Manville MVP - Microsoft Excel, Oxford,
England, says that there is an Excel 4 command that works:
Click to expand...

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
 
Back
Top