Expand my Community achievements bar.

Reportbuilder : Add VBA support to trigger data refresh from a macro

Avatar

Level 2

3/7/12

ReportBuilder is a great tool to have automated reports but is lacking a feature to make the automated report more powerful : make the data refresh part of a larger macro that can eventually render the final report more complete and then sky is the limit with what you can do. Obviously that feature would only apply for people refreshing their data locally (since macros will not run from the Adobe Reportbuilder servers for scheduled reports).

 

Example: integrate the ReportBuilder data refresh in a larger process that refreshes pivot tables, sort data, make external database connection to retrieve additional data (...). These macros can then do the little things to make a great report (e.g. only display the top bounce rates for pages that have more than xxx entries, merge natural/paid search keyword data in a single table, add external data like number of clients/accounts in database to compare with the additional clients from SiteCatalyst report)... The goal is to minimise all manual work to get the dahsboard that people need as with too many manual steps, people just either waste their time or don't do the task and have to cope with a sub-optimal report.

 

It is important to note that this exact same feature was available with ExcelClient (it was possible to trigger data refresh using VBA and query table refresh), so this feature request is essentially to get back a feature that was available with Excel Client...

9 Comments

Avatar

Level 1

5/10/12

Agree 100%.  I was going to request this very thing.  Maybe it is possible to expose the guts of Reportbuilder through a VBA API that would allow calls to its internal library of functions. 

Avatar

Level 1

12/12/12

Hear hear!

 

I love the requestor's idea but I'll suggest an alternate approach:  allow data refresh to be invoked by an Excel cell value.

 

In other words, let the user create a dependent request (functionality which already exists) but instead of depending on another data block, it depends on an Excel cell.  If A1 = true then refresh, else don't refresh.

 

That might be a quick n dirty way to accommodate the do-it-yourselfers while not incurring too much dev cost for Adobe.

 

I'd still love to see ReportBuilder methods made available in VBA though.

Avatar

Level 1

3/14/13

This comment will be long and technical in nature but will hopefully provide a sneak preview of what you will be able to do from VBA in Excel with ReportBuilder:

 

Prerequisite: the user needs to have logged into ARB before invoking the any of the following macros functions. 1) The following macro will refresh all ARB requests in the Active workbook. Sub RefreshAllReportBuilderRequests() Dim addIn As COMAddIn Dim automationObject As Object Dim success As Boolean 'First step is to invoke the ReportBuilder COM Addin through its Product ID Set addIn = Application.COMAddIns("ReportBuilderAddIn.Connect") Set automationObject = addIn.Object ' Once the Addin has been retrieved, invoke the RefreshAllRequests() API command success = automationObject.RefreshAllRequests(ActiveWorkbook) End Sub 2) The following macro will refresh all ARB requests in the Active worksheet Sub RefreshAllReportBuilderRequestsInActiveWorksheet() Dim addIn As COMAddIn Dim automationObject As Object Dim success As Boolean 'First step is to invoke the ReportBuilder COM Addin through its Product ID Set addIn = Application.COMAddIns("ReportBuilderAddIn.Connect") Set automationObject = addIn.Object ' Once the Addin has been retrieved, invoke the RefreshWorksheetRequests() API command success = automationObject.RefreshWorksheetRequests(ActiveWorkbook.ActiveSheet) ' The RefreshWorksheetRequests() API call take a worksheet object as an argument. ' Thus you can call if for any worksheet that contains ARB requests. End Sub 3) The following macro refreshes all ARB requests whose cell outputs intersect the  a specified range of cells Sub RefreshAllReportBuilderRequestsInCellsRange() Dim addIn As COMAddIn Dim automationObject As Object Dim success As Boolean 'First step is to invoke the ReportBuilder COM Addin through its Product ID Set addIn = Application.COMAddIns("ReportBuilderAddIn.Connect") Set automationObject = addIn.Object ' Once the Addin has been retrieved, invoke the RefreshRequestsInCellsRange() API command ' The cell range specified below points to the range B1:B54 of the "Data" worksheet of the active workbook. ' The range expression will support all supported Excel Range expression success = automationObject.RefreshRequestsInCellsRange("'Data'!B1:B54") End Sub

Avatar

Level 1

4/10/13

An alternative command that can be used to refresh the worksheet's requests in case you experience problems with the aforementioned code samples:

 

Application.CommandBars("Adobe ReportBuilder Toolbar").Controls("Refresh").Execute