Fast on the heels of the lat­est report builder release, I wanted to kick off a new series of blog posts that focuses on the power users of Excel and report builder—and hope­fully help gen­eral ana­lysts become power users in the process. With the recent intro­duc­tion of real-time reports, there is a greater need for users to cre­ate Excel work­books that can auto-refresh. Imag­ine cre­at­ing a fully cus­tomized and visu­al­ized dash­board of data in Excel that refreshes at reg­u­lar inter­vals with­out need­ing to engage in con­sult­ing or heavy soft­ware devel­op­ment. The poten­tial added value and cost sav­ings could be tremen­dous. So let’s dive in on how this can be done today.

Report Builder and Macros

Ear­lier this year, report builder intro­duced sup­port for per­form­ing sim­ple com­mands such as refresh via Excel VBA macros. Now if you use macros to per­form bulk tasks or basic automa­tion, you can include report builder func­tions in that work­flow with some sim­ple com­mands. For secu­rity rea­sons, you can­not sched­uled macro-enabled work­books for reg­u­lar deliv­ery from Adobe Ana­lyt­ics, but you can still save and use them locally. For the pur­poses of our objec­tives, we will lever­age the “RefreshRe­questsIn­Cell­sRange” along with the “Application.OnTime” func­tion calls in VBA. A com­plete set of the exam­ple code can be found at the end of this post.

Step 1: Gather Para­me­ters for the Refresh Process

There are three things we need to know to cre­ate an auto-refreshing dashboard:

  1. The range of cells that include the rel­e­vant requests, unless in rare cir­cum­stances you refresh all requests using the “Refre­shAll­Re­quests” function.
  2. The inter­val at which you want to refresh the requests. It is impor­tant to pre­vent your macro from refresh­ing too often to avoid throt­tling issues.
  3. The ini­tial start time for the process to begin. This is not nec­es­sary if the refresh­ing needs to begin immediately.

You can either man­u­ally code these para­me­ters into your macro, or you can uti­lize input boxes to gather this infor­ma­tion when the macro is run. The lat­ter is a more com­pli­cated approach, so I will show how this could be done.

First, we need to launch the Visual Basic edi­tor and insert a new mod­ule into our work­book project.

Screenshot inserting a new module

Insert a new mod­ule into your workbook

Then, before writ­ing any new func­tions, we need to cre­ate a cou­ple of global vari­ables to store our data para­me­ters because we will be using mul­ti­ple func­tions to accom­plish our task.

Public range As String
Public minstr As String

You can name these vari­ables what­ever you like, of course, but in this exam­ple, “range” will con­tain the cell selec­tion val­ues to indi­cate where the requests we want to auto-refresh are located. The “min­str” vari­able con­tains a value that rep­re­sents the inter­val between each refresh; in this case it is min­utes, but you can repur­pose this to define an inter­val in sec­onds as well, depend­ing on your needs. Next we need to cre­ate the ini­tial func­tion for col­lect­ing the para­me­ters we need. First, we declare this function:

Sub autoRefresh()

Then, we ask the user to spec­ify the range of cells that con­tains the requests we want to refresh:

On Error Resume Next
Set rRange = Application.InputBox(Prompt:= _
"Please select a range that contains requests you wish to auto-refresh.", _
Title:="Specify Refresh Range", Type:=8)
On Error GoTo ender:

An Input­Box of type 8 indi­cates a cell-selection input that will be passed into the “rRange” vari­able. The last line is meant to deal with any errors we might receive by going to “ender:” which is an arbi­trary line in the code at the end of the sub­rou­tine to sim­ply exit the func­tion. After this, we can col­lect the refresh interval:

On Error Resume Next
minutes = Application.InputBox(Prompt:= _
"Please enter the delay between refreshes in whole minutes (01-59).", _
Title:="Specify Refresh Interval", Type:=1)
On Error GoTo ender:

In this exam­ple, I cap­ture the inter­val value as a num­ber (i.e. Type:=1) so I can per­form error check­ing, but you can choose to cap­ture this value as a sim­ple text value (Type:=2) if that works bet­ter for you. Lastly, we can ask the user to input the ini­tial time for the auto-refresh process to start. This is par­tic­u­larly use­ful for tim­ing your requests to refresh on spe­cific marks, such as at the begin­ning of every hour.

On Error Resume Next
fstrTime = Application.InputBox(Prompt:= _
"Please enter the first time you wish to run the refresh (hh:mm:ss).", _
Title:="Specify Refresh Time", Type:=2)
On Error GoTo ender:

Note that the for­mat of the time­stamp in this input (hh:mm:ss) could also be applied when ask­ing for the inter­val between refreshes. Now that I have all the infor­ma­tion I need, I first extract the value of the selected cell range and trans­late the ini­tial start time into a TimeValue object:

range = rRange.Address
'Set the next refresh time based on interval specified
fTime = TimeValue(fstrTime)

Then, we take advan­tage of the Application.OnTime Excel func­tion to set up the first refresh:

Application.OnTime fTime, "performRefresh"

The “per­form­Re­fresh” part of this line of code refers to the func­tion we will be cre­at­ing in the next sec­tion and will per­form the actual refresh­ing of the requests.

Step 2: Set up the Auto­matic Refresh Function

Cre­ate a new sub­rou­tine called “per­form­Re­fresh” and, based on the doc­u­men­ta­tion, invoke the nec­es­sary com­po­nents to access report builder from a macro:

Sub performRefresh()

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

Now, using the range and inter­val val­ues stored in the global vari­ables you cre­ated ear­lier, invoke the refresh com­mand and set up the appli­ca­tion to recur­sively call this same func­tion again at the next appro­pri­ate time:

success = automationObject.RefreshRequestsInCellsRange(range)

'Set the next refresh time based on interval specified
dTime = Now + TimeValue("00:" & minstr & ":00")
Application.OnTime dTime, "performRefresh"

As a reminder, this exam­ple illus­trates spec­i­fy­ing a minute-based inter­val, but this last piece essen­tially com­pletes the work for an auto-refreshing work­book. As long as the appli­ca­tion stays open, this macro will con­tin­u­ously run in the back­ground until the file is closed.

Step 3: Test and Then Go

Because your computer’s local clock may dif­fer slightly from the server sys­tem time, set­ting up your macro to run at the top of every hour may not return the data set from the desired time period. I would rec­om­mend you man­u­ally refresh your real-time requests a few times to under­stand how your com­puter clock dif­fers and then account for that dif­fer­ence when you run this macro.

screenshot of a real-time request

Real-time request vs. Sys­tem Clock

Hope­fully, this arti­cle helps you under­stand some poten­tially use­ful appli­ca­tions of real-time data and macros in con­junc­tion with report builder. Feel free to reach out to me or our con­sult­ing team to explore how you can best lever­age report builder in your workflows.

Com­plete Exam­ple Mod­ule Code:

Public range As String
Public minstr As String

Sub autoRefresh()
' autoRefresh Macro
' by Eric Hansen
' Copyright (c) 2013 Adobe Systems, Inc.

Dim minutes As Integer
Dim errorMsg As String

On Error Resume Next
Set rRange = Application.InputBox(Prompt:= _
"Please select a range that contains requests you wish to auto-refresh.", _
Title:="Specify Refresh Range", Type:=8)
On Error GoTo ender:

On Error Resume Next
minutes = Application.InputBox(Prompt:= _
"Please enter the delay between refreshes in whole minutes (01-59).", _
Title:="Specify Refresh Interval", Type:=1)
On Error GoTo ender:

On Error Resume Next
fstrTime = Application.InputBox(Prompt:= _
"Please enter the first time you wish to run the refresh (hh:mm:ss).", _
Title:="Specify Refresh Time", Type:=2)
On Error GoTo ender:

Application.DisplayAlerts = True

If minutes < 1 Or minutes > 59 Then
errorMsg = errorMsg & "Invalid interval specified, must be between 1-59. "
GoTo ender:
Else
If minutes < 10 Then
minstr = "0" & minutes
Else
minstr = minutes
End If
End If

If rRange Is Nothing Then
errorMsg = errorMsg & "Invalid range selected. "
GoTo ender:
Else
range = rRange.Address
'Set the next refresh time based on interval specified
fTime = TimeValue(fstrTime)
Application.OnTime fTime, "performRefresh"
End If
Exit Sub

ender:
MsgBox "Macro stopped due to error/cancel. " & errorMsg, vbOKOnly, "Macro Stopped"
Exit Sub
noError:
End Sub

Sub performRefresh()
' performRefresh Macro
' by Eric Hansen
' Copyright (c) 2013 Adobe Systems, Inc.

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 captured range from the autoRefresh() macro.
' The range expression is compatibile with all supported Excel Range expressions
success = automationObject.RefreshRequestsInCellsRange(range)

'Set the next refresh time based on interval specified
dTime = Now + TimeValue("00:" & minstr & ":00")
Application.OnTime dTime, "performRefresh"
End Sub

3 comments
Cameron22
Cameron22

Hi guys,


I keep getting a "Run-time error '13': Type mismatch". Anyone knows whats going wrong?

Billy Sandstrom
Billy Sandstrom

This was very helpful, worked great as I was setting up. However once I change the request, macro is locked out of the sheet with a protect sheet call. Is there a way to get around disable the protect call so that the macro can run?