Refresh external data in Excel 2010 while the file is open in the background but not in use

excel excel-2010 excel-addins exceldatareader vba
hoz picture hoz · Nov 12, 2012 · Viewed 11.9k times · Source

I am using Excel 2010 to query MS SQL databases and other Excel files to import data. I have set different intervals for each data connection (10 connections). In excel this data is sliced and diced to create meaningful charts which are presented in PowerPoint.

I was able to find a VB ad-in for PowerPoint to update data from excel automatically while the presentation is being looped.

I keep the excel file open in the background (Behind PowerPoint) since PowerPoint tries to open the excel sheet every time a chart is being refreshed. My issue is in Excel, since it is not actively used the data connections do not refresh automatically.

I have tried various solutions, but they require the vb / macro to be initiated manually, however I need this to refresh data automatically based on the timings provided for the data connections.

Is there a VB code I can use in Excel to refresh the data connections while the excel workbook is opened but not actively used?

Appreciate if you could point me to a relevant post, or guide me.

Thank you in advance.

Answer

hoz picture hoz · May 19, 2015

I found the solution to refresh the Pivot cache if the data in a sheet changes. I'm updating this post in case anyone else needs the solution. Thank you very much for your help. 

Private Sub Worksheet_Change(ByVal Target As Range) 

Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh

End Sub

Follow this link for further information on this issue - http://www.get-digital-help.com/2011/06/13/auto-refresh-a-pivot-table-in-excel/#comment-52745

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Insert picture into Excel cell

I'm tying to generate a report with pictures, but I cannot get the pictures into a single cell. I can get the pictures to "float" around my worksheet, but I need to put them into a cell. How can I …

Read More