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

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 possilbe

John Wirt wrote:
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?

Great question!

1) VBA does not have a method of its own to change the update method of a
link
2) You can't change a file which is closed.

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)"

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.

Now you've got me confused.
You are now talking about changing to automatic updating.
If you can't work out how to do what you want with the above help then
please clarify at what stage you want the macro to run and whether it is
changing the links to manual or automatic?


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

Bill,

Thank you for replying. It looks like you may have it here.

I want the macro to change updating from automatic to manual in a workbook
after that workbook has been created by an author and closed, but before it
is opened by someone else for purposes of review..

I want the macro to autorun when the workbook is opened by someone other
than the author and change the updating to manual before the links created
by the author are automatically updated.

The sequence is this:

1. The workbooks I am concerned about are created by authors from a template
I give them. Often, the authors link their workbooks to external data
sources.
2. The workbooks submitted by the authors are reviewed by others. Generally,
the data sources that are linked to these workbooks are not submitted with
the workbooks. The data source files are too big.
3. The problem is that when the workbooks are opened by others, they
sometimes forget to answer "No" to the update links prompt.. When this
happens, the data in the workbook can become corrupted (lot's of #REF cell
values, etc.). To eliminate this problem, I want to change the template on
which these workbooks are based (or manually run the macro on all workbooks
that are submitted) so that any external links created in them by the
authors only update manually.
4. We use Excel 2000 and I see no way of creating links that can be set to
update manually. Automatic updated seems to be wired into Excel 2000.
Edit-:Links has a options for Automatic and Manual updating but the Manual
option is dimmed out -- even in the workbook when the links are created.
4. It would be feasible to manually run the macro I am looking for in all
workbooks when they are submitted by authors to change the updating to
manual.
5. As a last restor, I suppose we could change recalculation to manual but
that would be very inconvenient for most people.

This is the design problem:
a. Excel 2000.
b. Write a macro stored in a module in the template used to create many
workbooks that can be manually run to switch the updating of external links
created in the workbook to manual.
c. While one is at it, it probably would be a good idea to have another
macro for toggling link updating back to automatic.

I know, I know, one solution to the problem is to buy Excel 2003. Not
feasible at this time.

John Wirt

John
"Bill Manville" wrote in message
...
John Wirt wrote:
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 meant here that the workbook has been closed by the person who created it
and the external links.

I want the macro to change the updating to manual before the next person
opens the workbook.



Great question!

1) VBA does not have a method of its own to change the update method of a
link
2) You can't change a file which is closed.

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)"

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.

Now you've got me confused.
You are now talking about changing to automatic updating.
If you can't work out how to do what you want with the above help then
please clarify at what stage you want the macro to run and whether it is
changing the links to manual or automatic?


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

Bill,

In your code, I do not understand how to dimension the variable, V. If I
dimension it as an array, the VBA editor complains that "V" is not an array
variable in the first statement. If I omit any dimension statement, VAB
complains "Type Mismatch" on the second statement (as it should).

John

V = ThisWorkbook.LinkSources(xlOLELinks)
' change to manual updating
ExecuteExcel4Macro "SET.UPDATE.STATUS(""" & V(1) & """,2,2)"


"Bill Manville" wrote in message
...

If manual is dimmed in Edit=Links, then you won't be able to change it with
code. Note that Bill's code was for OleLinks, not Excel Links.

Your idea is flawed, because the prompt to update links is done before any
macro is run.


You can use a master workbook that opens the other workbooks using code -
then you can open them with the UpdateLinks option set in the open method.
That is the only feasible solution I see.

--
Regards,
Tom Ogilvy



"John Wirt" wrote in message
...
Bill,

Thank you for replying. It looks like you may have it here.

I want the macro to change updating from automatic to manual in a workbook
after that workbook has been created by an author and closed, but before
it
is opened by someone else for purposes of review..

I want the macro to autorun when the workbook is opened by someone other
than the author and change the updating to manual before the links created
by the author are automatically updated.

The sequence is this:

1. The workbooks I am concerned about are created by authors from a
template
I give them. Often, the authors link their workbooks to external data
sources.
2. The workbooks submitted by the authors are reviewed by others.
Generally,
the data sources that are linked to these workbooks are not submitted with
the workbooks. The data source files are too big.
3. The problem is that when the workbooks are opened by others, they
sometimes forget to answer "No" to the update links prompt.. When this
happens, the data in the workbook can become corrupted (lot's of #REF cell
values, etc.). To eliminate this problem, I want to change the template on
which these workbooks are based (or manually run the macro on all
workbooks
that are submitted) so that any external links created in them by the
authors only update manually.
4. We use Excel 2000 and I see no way of creating links that can be set to
update manually. Automatic updated seems to be wired into Excel 2000.
Edit-:Links has a options for Automatic and Manual updating but the
Manual
option is dimmed out -- even in the workbook when the links are created.
4. It would be feasible to manually run the macro I am looking for in all
workbooks when they are submitted by authors to change the updating to
manual.
5. As a last restor, I suppose we could change recalculation to manual but
that would be very inconvenient for most people.

This is the design problem:
a. Excel 2000.
b. Write a macro stored in a module in the template used to create many
workbooks that can be manually run to switch the updating of external
links
created in the workbook to manual.
c. While one is at it, it probably would be a good idea to have another
macro for toggling link updating back to automatic.

I know, I know, one solution to the problem is to buy Excel 2003. Not
feasible at this time.

John Wirt

John
"Bill Manville" wrote in message
...
John Wirt wrote:
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 meant here that the workbook has been closed by the person who created
it
and the external links.

I want the macro to change the updating to manual before the next person
opens the workbook.



Great question!

1) VBA does not have a method of its own to change the update method of
a
link
2) You can't change a file which is closed.

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)"

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.

Now you've got me confused.
You are now talking about changing to automatic updating.
If you can't work out how to do what you want with the above help then
please clarify at what stage you want the macro to run and whether it is
changing the links to manual or automatic?


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

John Wirt wrote:
In your code, I do not understand how to dimension the variable, V.

Dim V '[As Variant]

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