Excel/"Enable Automatic Refresh"
HeWhoWas wrote at 2008-04-10 08:06:09
I had the same issue as you and found an answer. Here is a function I designed that will alter the key in the registry that controls the "Enable Automatic Refresh" dialog.
Function EditRefreshSecurity(NewValue As Integer)
Dim objWSH As Object
key = "HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options\QuerySecurity"
Value_Type = "REG_DWORD"
Set objWSH = CreateObject("WScript.Shell")
objWSH.RegWrite key, NewValue, Value_Type
Set objWSH = Nothing
0 is the default, Excel will ask you if you want to refresh
1 = Do not ask, and do not refresh
2 = Do not ask, and ALWAYS REFRESH
So you could add this function at the start of the macro that loads the other workbooks as EditRefreshSecurity(2) and after you've done everything you can use EditRefreshSecurity(0) to reset the key to normal.
DeBeaux wrote at 2013-04-07 16:00:09
I discovered how toset up the registry to disable the opening query of the pop-up by setting the value to "2", and that takes care of the "POP-UP" that asks if you want auto refresh enabled or disabled.
What I think he is asking is how do you get the pivot tables to refresh when NEW data arrives...
I borrowed a VB script to take care of that:
When New datais added to a range in the current Worksheet (you can have multiple) structure the pivot table to use the range instead of the individual cells. That way if the range expands via the query, it will include the new data.
Using the script:
Private Sub Worksheet_Change(ByVal Target As Range)
' Update the pivot table on this worksheet if data in columns D or E is changed
If (Target.Column = 1) Then
Further, since the data on that worksheet is sent to a overview worksheet in the same workbook, the "overview" worksheet has another VB:
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
So in summary, I have 8 worksheets, 7 that have web queries that refresh every 15 minutes. The I have a VB on EACH worksheet that tells Excel to refresh the pivot table if the data from the query changes the value of the range by adding data.
When all that is done, the overview refreshes ALL worksheets to update the "Overview" worksheet.
All I have to do is start pen the workbook, and it does its thing.
I picked and poked, and I did not do this by myself, but thought be putting it all together, I might help someone else.