How to auto acknowledge a prompt box, Macro

[gifer]

Hi,

I have a Excel sheet that uses Get External Data to draw charts. I have the
Data set up to refresh every 5 min., and likewise be reflected in my
continuous line charts.

I need to access the "up to date" data from a couple different locations, so
my file is on a common drive on our network. In order for the file to be up
to date when I open a new instance of it from another PC, I have the base PC
perform an Auto save every 10 minutes.

Occasionally, the auto save and the refresh try to execute at the same time,
and I get a prompt box telling me that "This command will stop
refresh/update scan, OK?" (or something very similar)... Anyway, if I am not
at my PC when that happens, the refresh (nor the save) will occur until
either "OK", or "Cancel" is clicked. How can I write a macro that will
automatically choose "OK"?

Thanks,

John

 

[gifer]

Forgot... I'm using Excel2000

 

[NickHK]

gifer,
What about setting the .BackGroundRefresh to false, and doing your .Save and
..Refresh in the OnTime event.

NickHK

 

[gifer]

I'm not very good with macros, but sounds like you are saying to have code
in a macro to run based on "time", and when it runs, do the refresh, and
then save so neither are occurring at the same time, rather one before/after
the other.

..BackGroundRefresh... is that in a macro? I can uncheck it in the Get
External Data setup. I am not using macros in this worksheet yet... so I
will need a quick jump start to get suggestion into a simple (new) macro.

It does sound like the idea you show will do what I need.

Thanks,

John

 

[NickHK]

gifer,
Right click somewhere in your range of data that refreshes and click the
menu option "Data range Properties" and uncheck "Enable Background Refresh".
For the .Save code, record a macro whilst doing the procedure.
Application.OnTime can be found here
http://www.cpearson.com/excel/ontime.htm

Put it all together.
Give it a try and post back if you have difficulties.

NickHk

 

[gifer]

OK. Thanks a lot. I have everything working fine. I want the macro to start
running when I open the file, how and where do I use an event procedure like
workbook_open (if this is a real one??) to do this?

John

 

[NickHK]

gifer,
Workbook_Open would be a good place.

NickHK