vba 如何使用 EPM 引用刷新 Excel 工作簿中的多个工作表（按特定顺序）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24659526/
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
How can I refresh multiple sheets (in specific order) in an Excel workbook using EPM references?
提问by Sean T.
I have a workbook with about 50 sheets to be refreshed in a certain order (to avoid #rfr errors, since the sheets build off of one another).
我有一个大约有 50 张工作簿，要按特定顺序刷新（以避免 #rfr 错误，因为这些工作表是相互叠加的）。
The refresh is done via the EPM add in for Excel. I have activated the FPMXLclient functions and have attempted to write some code. I am very inexperienced with coding and logic. In the workbook the macro needs to start at the last tab, wait for the sheet to refresh, then move on to the next tab (and so on...). Below is an example of some of the VBA code I have written:
刷新是通过 Excel 的 EPM 插件完成的。我已激活 FPMXLclient 函数并尝试编写一些代码。我对编码和逻辑非常缺乏经验。在工作簿中，宏需要从最后一个选项卡开始，等待工作表刷新，然后移动到下一个选项卡（依此类推...）。下面是我编写的一些 VBA 代码的示例：
Dim refreshList
refreshList = Array("BS Analytic", "Balance Sheet")
'There are more than just the 2 in the array (~50)
Sub test_loop()
Dim I
For I = LBound(refreshList) To UBound(refreshList)
MsgBox refreshList(I)
Next I
End Sub
'Vba to refresh data
Dim client As New EPMAddInAutomation
Sub Refresh_Click()
client.Refresh
End Sub
Sub AFTER_REFRESH()
MsgBox "done"
End Sub
Other info: This involves BPC and SAP too.
其他信息：这也涉及 BPC 和 SAP。
回答by Moiety Design
Why not have each sheet number in the array refreshListand then use For each I in refreshList. That will then propagate the refreshListin the order for each update. If it is also moving from the last sheet backwards you could always do the following:
为什么不在数组中包含每个工作表编号refreshList，然后使用For each I in refreshList. 然后将按refreshList每次更新的顺序传播。如果它也从最后一张纸向后移动，您始终可以执行以下操作：
Sub Refresh_Click
Dim refreshList() As Integer
reDim refreshList(50)
for i = 0 to 49
refreshList(i) = 50 - i
next
For each I in refreshList
Sheets(I).EnableCalculation = false
Sheets(I).EnableCalculation = true
Next
MsgBox "Done"
End Sub
回答by stenci
Assuming that your addin refreshes the active sheet, something like this in your loop might work:
假设您的插件刷新了活动工作表，循环中的类似内容可能会起作用：
Dim Sh As Worksheet
Set Sh = WorkSheets(RefreshList(I))
Sh.Activate
Client.Refresh
