Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Prompt | Excel Discussion (Misc queries) | |||
Auto rename and save prompt needed. | Excel Worksheet Functions | |||
Auto-prompt/complete (?) function for number values | Excel Discussion (Misc queries) | |||
How to acknowledge a message box from VB program? | Excel Programming | |||
Macro Prompt | Excel Programming |