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 or ensure your login is set to “Remember Me”.
Important: For security reasons, you cannot schedule a workbook that contains a macro through the report builder scheduling interface.
Refresh All ReportBuilder Requests
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
Refresh All ReportBuilder Requests In Active Worksheet
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
Refresh All ReportBuilder Requests In Cells
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
