vba 使用 PowerPivot 自动刷新代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19052081/
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
Using the PowerPivot automatic refresh code
提问by BMene
I have a powerpivot-based model, and was looking through the web to automate the process of refreshing the graphs without having to open the powerpivot window, and then clicking on the "Refresh All" button (I'm using Excel 2010). I came across the VBA code written by Goban Saor on this blog: http://blog.gobansaor.com/vba-code-to-automate-a-powerpivot-refresh/The problem is that I don't know enough about VBA to make the code provided on the website work with my spreadsheet, and I was wondering if anybody could help me. Which inputs in the code do I need to change to make it work with my specific file on Excel 2010 (if any)? The name of my Excel file is PTM.xlsm, and the name of the pivot table is Table1. Will the VBA code go on the Workbook script, or under the specific sheet? Since I have 5 sheets linked to the same underlying powerpivot, do I need to change anything in the code to account for that?
我有一个基于 powerpivot 的模型，并且正在浏览网络以自动化刷新图形的过程，而无需打开 powerpivot 窗口，然后单击“全部刷新”按钮（我使用的是 Excel 2010）。我在这个博客上遇到了 Goban Saor 写的 VBA 代码：http: //blog.gobansaor.com/vba-code-to-automate-a-powerpivot-refresh/问题是我对 VBA 不够了解使网站上提供的代码适用于我的电子表格，我想知道是否有人可以帮助我。我需要更改代码中的哪些输入才能使其适用于我在 Excel 2010 上的特定文件（如果有）？我的 Excel 文件的名称是PTM.xlsm，数据透视表的名称是Table1. VBA 代码会在工作簿脚本上，还是在特定工作表下？由于我有 5 个工作表链接到同一个底层 powerpivot，我是否需要更改代码中的任何内容来解决这个问题？
Thank you for the help ahead of time!
提前感谢您的帮助！
回答by Petr Havlik
I was having the same issue and somebody here suggested in a different thread this little project on Codeplex: Excel and PowerPivot Refresh Suite.
我遇到了同样的问题，这里有人在 Codeplex 上的这个小项目的不同线程中提出了建议：Excel 和 PowerPivot Refresh Suite。
Hope this helps, even though I am not the one to take the credit for helping out :)
希望这会有所帮助，即使我不是那个因帮助而受到赞扬的人:)
回答by user401093
Do you have access to a SharePoint instance by any chance? If not, I highly recommend having one in place - it radically simplifies keeping Powerpivot documents refreshed. You could run into issues using VBA especially when users begin consuming the data.
您是否有机会访问 SharePoint 实例？如果没有，我强烈建议您安装一个 - 它从根本上简化了 Powerpivot 文档的更新。使用 VBA 时可能会遇到问题，尤其是当用户开始使用数据时。
回答by Dale Brubaker
I never did get the codeplex Excel and PowerPivot Refresh Suite to work properly. Here is a really simple AutoHotkey script that you can run on a task schedule:
我从来没有让 codeplex Excel 和 PowerPivot Refresh Suite 正常工作。这是一个非常简单的 AutoHotkey 脚本，您可以按任务计划运行它：
#NoEnv ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn ; Enable warnings to assist with detecting common errors.
SendMode Input ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.
myFile = %A_ScriptDir%\JIRA 2013 v1.xlsx
oExcel := ComObjCreate("Excel.Application")
oExcel.Visible := 1
oWorkbook := oExcel.Workbooks.Open(myFile)
oWorkbook.RefreshAll()
oWorkbook.Save()
oExcel.quit
