AutoRefresh excel data (Through SAP BO Analysis)

[Former Member]

Hi,

I have an excel sheet with data loaded through SAP BO (Analysis plugin). Everytime I open the sheet, I need to choose the prompt and then manually click on refresh to get the latest data (although the autorefresh on opening the file toggle is selected).

My question though, is how can I set it to automatically refresh the data at a set interval after I opened the file ? Can I add any VBA code to do that ? If so, can you share what code to use ?( Note that I have hardly any skills in coding, so I can only copy/paste the code you share with me 🙂 )

[Former Member]

Hi,

this VBA Code works fine for me. After a double click on the workbook which is saved on my desktop, the AO Add-In starts and the datasource refresh automatically.

Sub EnableAnalysisOffice()

Dim addin As COMAddIn

For Each addin In Application.COMAddIns

If addin.progID = "SapExcelAddIn" Then

If addin.Connect = False Then addin.Connect = True

End If

Next

End Sub

Sub BWLogon()

Dim lResult As Long

lResult = Application.Run("SAPLogon", "Your Datasource, for example DS_1", "Your Mandant", "Your User", "Your Password", "Your Language")

lResult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")

End Sub

Sub Workbook_open()

Call EnableAnalysisOffice End Sub

Sub Workbook_SAP_Initialize()

Call BWLogon

End Sub

I use AO 2.3.3

Best Steffen

[Former Member]

Thanks Tammy, but unforutnately it's not clear enough for me.

[TammyPowlas]

You can also look at David Stocker's blog here: https://blogs.sap.com/2013/09/24/roll-your-own-analysis-office-features-part-ii-using-the-autorefres...

[TammyPowlas]

Hi - a quick search shows contributor Martin Kreitlin's blog here: https://blogs.sap.com/2015/09/09/best-practices-for-vba-in-sap-bi-analysis-for-ms-excel/ - please review it to see if helps