You are here:

Excel/"Enable Automatic Refresh"

Advertisement


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

   

End Function



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

ActiveSheet.PivotTables(1).RefreshTable

End If

End Sub



Further, since the data on that worksheet is sent to a overview worksheet in the same workbook, the "overview" worksheet has another VB:

Sub AllWorkbookPivots()



Dim pt As PivotTable

Dim ws As Worksheet

   For Each ws In ActiveWorkbook.Worksheets

       For Each pt In ws.PivotTables

         pt.RefreshTable

       Next pt

   Next ws

End Sub



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.


About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Miguel Zapico

Expertise

I am recycling to Excel 2010, so I am taking an extended leave while I work on keeping myself an expert on this matter.

Experience

I have worked with Excel for the past 12 years, in various environments.

Organizations
NYPC (New York PC users group)

Organizations
NYPC (New York PC users group)

Education/Credentials
MCSE in Windows NT

©2016 About.com. All rights reserved.