Query Connection Property - Refresh every 'n' minutes.

carpetony

New Member
Joined
May 26, 2008
Messages
12
I run an SPC chart with a power query created table that tests results against Nelson Rules. The samples runs fairly quickly, so I have the Refresh every set to 2 minutes during the day.

The refresh triggers a Worksheet_Change event that checks the timestamp of the most recent run, to the last run, and if they are not the same, it triggers events to FTP the chart image to a local intra-web where ops can see the results throughout the day on a monitor at the fixture station. (The macro also then records the value 'recent run timestamp' to the 'last run timestamp' for the subsequent refresh).

My question is on the Refresh every: I would like to change the 'n' for minutes so it can slow down at the lunch time break, maybe take it to "15" minutes. And then at EOD down to "60" (1 hour), and overnight to "360" (6 hour) until 6AM where it kicks back to 2 minutes.

I have had great success changing the Query Connection String (Definition Tab, Command Text) on the fly when I've done straight queries into the table and I need to bounce from fixture to fixture and different result types--but I don't see anything for the refresh rate when I dig around in the Watch Window. I didn't know if anyone had anything I wasn't seeing.

Note:
On another SPC workbook, I do just set the updating macro to run at distinct times throughout the day--like on the hour for control standards. But that seems really cumbersome considering the rate at which this particular sample runs, especially considering I can just have the query refresh itself.


refresh.JPG


Thanks.
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Watch MrExcel Video

Forum statistics

Threads
1,123,260
Messages
5,600,586
Members
414,391
Latest member
Pandurang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top