VBA macros, also known as Visual Basic Macros, allow you to manipulate workbooks in ways Microsoft Excel alone cannot. Visual Basic has access to the workbook, Excel, and even Windows.

As of March 21 2013, VBA can call three report builder API methods. To use these API calls, ensure that the latest version of report builder is installed, and log in before running any macros.

Important: For security reasons, you cannot schedule a workbook that contains a macro through the report builder scheduling interface.

For security reasons, a workbook that contains a macro cannot be scheduled through the report builder scheduling interface. 

RefreshAllReportBuilderRequests()

This macro refreshes all report builder requests in the active workbook. It starts by calling the report builder COM Add-in through its Product ID, then calls the RefreshAllRequests() API command:

Sub RefreshAllReportBuilderRequests()

 Dim addIn As COMAddIn
 Dim automationObject As Object
 Dim success As String
 Set addIn = Application.COMAddIns("ReportBuilderAddIn.Connect")
 Set automationObject = addIn.Object
 success = automationObject.RefreshAllRequests(ActiveWorkbook)

End Sub

RefreshAllReportBuilderRequestsInActiveWorksheet()

This macro refreshes all report builder requests in the active worksheet. The RefreshWorksheetREquests() API call takes a worksheet object as an argument. You can use this call for any worksheet that contains report builder requests:

Sub RefreshAllReportBuilderRequestsInActiveWorksheet()

 Dim addIn As COMAddIn
 Dim automationObject As Object
 Dim success As String
 Set addIn = Application.COMAddIns("ReportBuilderAddIn.Connect")
 Set automationObject = addIn.Object
 success = automationObject.RefreshWorksheetRequests(ActiveWorkbook.ActiveSheet)

End Sub

RefreshAllReportBuilderRequestsInCellsRange()

This macro refreshes all report builder requests whose cell outputs intersect the specified range of cells. The cell range used in this example points to the range B1:B54 of the "Data" worksheet within the active workbook. The range expression supports all supported Excel range expressions:

Sub RefreshAllReportBuilderRequestsInCellsRange()

 Dim addIn As COMAddIn
 Dim automationObject As Object
 Dim success As String
 Set addIn = Application.COMAddIns("ReportBuilderAddIn.Connect")
 Set automationObject = addIn.Object
 success = automationObject.RefreshRequestsInCellsRange("'Data'!B1:B54")
 
End Sub

此産品由 Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License 授權  Creative Commons 條款未涵蓋 Twitter™ 與 Facebook 文章。

法律說明   |   線上隱私權政策