VBA Help

[Guest]

Hello,

Upon launching a workbook that has external links we are prompted to hit
update or don't update. Is there code that I use that will prompt update?

Thanks

 

[Guest]

Sorry, I'm not sure I get it: are you asking (a) how to trigger an update
programmatically, or (b) what may cause Excel to start updating a workbook
when you open it. I suppose it's the latter option? Please clarify.

/MP

 

[Guest]

A - trigger the update automatically

Sorry, I'm not sure I get it: are you asking (a) how to trigger an update
programmatically, or (b) what may cause Excel to start updating a workbook
when you open it. I suppose it's the latter option? Please clarify.

/MP

 

[Guest]

Okidoki... Well, have a look at the UpdateLink mehod of the Workbook class
(it can take optional params such as Name of file to check, and Type of link
as well).

Note that the Calculate() and CalculateFull() methods do not really work,
because in order to minimise its recalculation effort and thus maximise
speed, Excel tries to be a bit clever. If a cell formula has not changed it's
difficult to get it to update. External files that change are not monitored
by Excel in any way (however, if you happen to have both the primary file and
the linked file open at the same time in the same instance of Excel, the
links will update).

Or, if the worst comes to the worst, you can copy the content (formula) of
each cell, and then write the formulae back to the cells again

Anyhow, the following article is a discussion of how Excel determines which
cells to update:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlrecalc.asp

Cheers,
/MP

 

[Guest]

the prompt is rendered before any code runs.

In excel 2002/Office XP and later, there is an option in Edit=>Links. In
the dialog in the lower left corner there is a button labelled "Startup
Prompt". You can make some selections there.