Automate open file, update links, run macro, close and save file

[Geoff]

I have a spreadsheet that reads data from an SQL
database, manipulates it, and passes it back to a
Sharepoint list. I have developed a macro that does the
data manipulation and synchronization with Sharepoint.

I'd like to define a scheduled task that launches the
file. On file open, I want the macro to launch after the
external links have been refreshed and once the macro is
complete I want to quit and save the file.

I'm new to VB, so any help you can provide to point me in
the right direction would be greatly appreciated. Thanks
in advance.

 

[microsoft]

hello geoff,

you might add a timer object to your project that will
execute your macro every nn minutes or hours. i have
vb & vb.net installed on this computer so im not sure if
the timer object i use is available without vb.

to see if it is, in your vba editor, go to Tools >> References
and look for a timer ref.

-theHman

 

[Geoff]

How can I tell if all of the external (ODBC) links have
been updated? I'm looking for a while loop that
evaluates each of the links stored in the worksheet to
see if they are old. Once all have been refreshed, I can
take it from there. Also, I'm testing on Office 2003 and
the security has two security prompts when I open the
file: The first is to authorize updating links from
other Excel files, and the second is to enable automatic
refresh of the queries. I haven't figured out how to set
the security settings to bypass these prompts.

Geoff