vba 当文件在后台打开但未使用时刷新 Excel 2010 中的外部数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13348633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Refresh external data in Excel 2010 while the file is open in the background but not in use
提问by hoz
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.
我正在使用 Excel 2010 查询 MS SQL 数据库和其他 Excel 文件以导入数据。我为每个数据连接（10 个连接）设置了不同的间隔。在 excel 中，这些数据被切片和切块以创建在 PowerPoint 中呈现的有意义的图表。
I was able to find a VB ad-in for PowerPoint to update data from excel automatically while the presentation is being looped.
我找到了 PowerPoint 的 VB 插件，可以在演示文稿循环播放时自动从 excel 更新数据。
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.
我保持 Excel 文件在后台打开（在 PowerPoint 后面），因为 PowerPoint 每次刷新图表时都会尝试打开 Excel 工作表。我的问题是在 Excel 中，因为它没有被积极使用，数据连接不会自动刷新。
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.
我尝试了各种解决方案，但它们需要手动启动 vb/宏，但是我需要它根据为数据连接提供的时间自动刷新数据。
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?
是否有 VB 代码可以在 Excel 工作簿打开但未主动使用时在 Excel 中使用以刷新数据连接？
Appreciate if you could point me to a relevant post, or guide me.
感谢您能否将我指向相关帖子或指导我。
Thank you in advance.
先感谢您。
回答by hoz
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.
如果工作表中的数据发生变化，我找到了刷新 Pivot 缓存的解决方案。我正在更新这篇文章，以防其他人需要解决方案。非常感谢您的帮助。
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
点击此链接以获取有关此问题的更多信息 - http://www.get-digital-help.com/2011/06/13/auto-refresh-a-pivot-table-in-excel/#comment-52745
回答by ExactaBox
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"?
听起来您已经启用了此功能，但是当您转到数据...连接并选择您的连接/查询之一时，单击属性...，在使用选项卡下，您是否选择了“启用后台刷新”和“刷新”每 X 分钟”？
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.
即使程序没有焦点，Excel 也应继续更新其查询。在我撰写此回复时，我可以看到 Excel 在后台每分钟更新一次测试查询。
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.
有没有试过其他的场景……如果不是主动使用Excel表格，但是电脑空闲，刷新吗？如果你用IE浏览网页？用铬？即尝试诊断问题是否与 PowerPoint 相关。
This should not require VBA to get working.
这不应该需要 VBA 才能工作。
回答by Alex
See my answer here: https://superuser.com/questions/232656/how-do-you-update-an-excel-file-data-refresh-and-update-formulas-without-openi
在此处查看我的答案：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::RefreshIt 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.
tdlr，使用http://metacpan.org/pod/Win32::Excel::Refresh如果您不想使用 perl ，它带有 Windows EXE。这使您可以从命令行运行 Excel 更新/刷新/宏，并且您可以使用任务计划程序来运行它。
