How to Refresh SAP Analysis for Office Queries & Data Sources with a Macro

Depending on the report, having a way to automate your data source refreshes may save you a ton of time.

Contents:

1. How to Create a Macro to Refresh SAP AO Data Sources

2. Example - Refreshing a Daily SAP AO Report with a Macro

How to Create a Macro to Refresh SAP Analysis for Office Data Sources

Because SAP Analysis for Office is completely compatible with VBA, we can write a simple one-line macro that will refresh any data source in your workbook.

To create a new macro, open the VBA Editor by pressing Alt + F11, or going to the Developer Tab and click the "Visual Basic" button.

Next, we want to create a new Module to hold the code. right click on the VBAProject for your workbook and select "Insert New Module".

From here, we can drop in our VBA code that will refresh the queries and data sources.

The code that we want to be using in our Subroutine follows this structure:

Application.Run "SAPExecuteCommand", "Refresh", "data_source_name"

This line of code executes SAPExecuteCommand with the parameters "Refresh" and the "data_source_name". You'll need to rename the data_source_name to match the name of the data source or query want to refresh.

You'll need to use the technical data source name, like DS_1 or DS_2 in order for the macro to work.

The "Refresh" command for the SAP AO API causes a refresh of the data source with the name provided.

So, our full macro would be:

Sub Source_Refresh()

Application.Run "SAPExecuteCommand", "Refresh", "DS_1"

End Sub

If you have multiple sources to refresh at one time, you can simply list them out within this Sub:

Sub Source_Refresh()

Application.Run "SAPExecuteCommand", "Refresh", "DS_1"
Application.Run "SAPExecuteCommand", "Refresh", "DS_2"
Application.Run "SAPExecuteCommand", "Refresh", "DS_2"

End Sub

How to Refresh a Daily SAP Analysis for Office Data Report with a Macro

For a real-world example, let's say we have a daily sales report that we need to update by hand every day. Instead of going through the menus click refresh by hand, we can create a macro that we can run to refresh them.

Here is our daily sales report tied to the data source "DailySalesData", with the technical name, DS_8

To refresh this report using a macro, we can use the following code:

Sub Source_Refresh()

Application.Run "SAPExecuteCommand", "Refresh", "DS_8"

End Sub

When run, this will refresh the report and, if available, populate our report with updated data.

This is especially useful if you have reports with many different data sources and many different refreshes needed every day. You can create a macro like the one above to do them all automatically in one step.

You could then link the macro to a button for extra ease of use.