Automating Omniture Report Builder Refresh in Excel VBA

Question

I have an excel 2010 workbook with several Adobe Omniture Report Builder (5.0.50.0) queries in it. I'm scripting the update and distribution via email of this workbook in VBA and I'm having trouble getting the Report Builder portion to refresh.

Adobe provides the following code which doesn't work ;

Sub RefreshAllReportBuilderRequests()

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

End Sub

Changing 'Success' from boolean to String allows it to run but it still doesn't refresh anything. I've also tried calling the Adobe.ReportBuilder.Bridge worksheet function as;

Application.Run ("AsyncRefreshAll")

Again no effect. The only way I've been able to refresh the reports is by calling the execute method of the report builder add-in's refresh button of the ribbon and guessing how long it'll take to run.

Application.CommandBars("Adobe Report Builder Toolbar").Controls(5).Execute

Application.Wait (Now() + TimeValue("00:00:02"))

Anyone know of a better way to make this work?

Answer 1

I had the same issue last week, but now solved. You're right, success is not a boolean, it's a string. I notified Omniture about this and they changed their online documentation (but there's still the same issue in the two last codes : https://helpx.adobe.com/analytics/kb/visual-basic-macros-reportbuilder.html).

I changed the dim success as a string and now it works ! Here is the macro I use :

Sub RBRefresh()
    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

So I think it's not a macro issue anymore, maybe an Excel issue ?