How to auto acknowledge a prompt box, Macro
 

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

How to auto acknowledge a prompt box, Macro
 

Forgot... I'm using Excel2000
"gifer" wrote in message
...
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

How to auto acknowledge a prompt box, Macro
 

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

NickHK

"gifer" wrote in message
...
Forgot... I'm using Excel2000
"gifer" wrote in message
...
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

How to auto acknowledge a prompt box, Macro
 

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" wrote in message
...
gifer,
What about setting the .BackGroundRefresh to false, and doing your .Save
and
.Refresh in the OnTime event.

NickHK

"gifer" wrote in message
...
Forgot... I'm using Excel2000
"gifer" wrote in message
...
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

How to auto acknowledge a prompt box, Macro
 

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" wrote in message
...
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" wrote in message
...
gifer,
What about setting the .BackGroundRefresh to false, and doing your .Save
and
.Refresh in the OnTime event.

NickHK

"gifer" wrote in message
...
Forgot... I'm using Excel2000
"gifer" wrote in message
...
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

How to auto acknowledge a prompt box, Macro
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
gifer,
What about setting the .BackGroundRefresh to false, and doing your
.Save
and
.Refresh in the OnTime event.

NickHK

"gifer" wrote in message
...
Forgot... I'm using Excel2000
"gifer" wrote in message
...
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

How to auto acknowledge a prompt box, Macro
 

gifer,
Workbook_Open would be a good place.

NickHK

"gifer" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
gifer,
What about setting the .BackGroundRefresh to false, and doing your
.Save
and
.Refresh in the OnTime event.

NickHK

"gifer" wrote in message
...
Forgot... I'm using Excel2000
"gifer" wrote in message
...
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