CODEWITHSUNDEEP
excelexcel-2010excel-addinsexceldatareadervba
hoz
hoz

Reputation: 571

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

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.

Upvotes: 0

Views: 12056

Answers (3)

hoz
hoz

Reputation: 571

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

Upvotes: 1

Alex
Alex

Reputation: 948

See my answer here: https://superuser.com/questions/232656/how-do-you-update-an-excel-file-data-refresh-and-update-formulas-without-openi

tdlr, use http://metacpan.org/pod/Win32::Excel::Refresh It comes with a Windows EXE if you don't want to use perl. This lets you run an Excel updates/refresh/macro from the command line and you can use Task Scheduler to run it.

Upvotes: 0

ExactaBox
ExactaBox

Reputation: 3395

It sounds like you have this enabled already, but when you go to Data... Connections and select one of your connections/queries, click Properties..., under the Usage tab, have you selected "Enable background refresh" and "Refresh every X minutes"?

Excel should continue updating its queries even if the program does not have focus. As I write this reply, I can see Excel in the background updating a test query once per minute.

Have you tried other scenarios... if you are not actively using the Excel sheet, but the computer is idle, does it refresh? If you are browsing the web with IE? With Chrome? i.e. trying to diagnose if the problem is isolated to PowerPoint.

This should not require VBA to get working.

Upvotes: 0

Related Questions