I tried my best but didn't find any solution for my problem. I hope some of you can help me.
I automated the login and refresh for my Data Sources via VBA triggered by a "WorkbookOpen" Sub. The Login works fine but the refresh is kind of 'incomplete'. Formulas and filters are not working like they are when I refresh it manually.
My Workbook_Open routine is the following:
Private Sub Workbook_Open() ActiveWorkbook.RefreshAll Select Case MsgBox ("Choose Yes to log on to Analysis and refresh Data." & vbCrLf & _ "Choose No to continue without logging in or refreshing.", vbYesNo) Case vbYes Call Analysis_Logon Case vbNo End Select End Sub
The Analysis_Logon macro looks like this:
Sub Analysis_Logon() 'Declaration Dim lret as Boolean Dim lresult as Long Dim user as String Dim password as String Dim pathlogon as String 'routine to select the user and password from a mastersheet. I skip this here due to privacy but i know this is working fine. lret = Application.Run("SAPLogon", "DS_1", "001", user, password) 'For the refresh I tried different orders and combinations of the following commands lresult = Application.Run("SAPExecuteCommand", "Refresh") 'I also tried: lresult = Application.Run("SAPExecuteCommand", "Restart", "DS_1") 'and lresult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1") 'as well as lret = Application.Run("SAPGetProperty", "IsDatasourceActive, "DS_1") if lret = False Then lresult = Application.Run("SAPExecuteCommand", "Restart", "DS_1") lresult = Application.Run("SAPExecuteCommand", "Refresh", "DS_1") End if End Sub
It does refresh the Datasets but it doesn't get the formulas I use in my excel sheets to work. For example: I am using the the following formula to give the users not familiar with analysis an easy way to filter the datasets.
In order to interact with the data the cells with the filter formula need to get active an look like this:
But after my vba refresh it only looks like this:
I can't use the filter cells to select any components. In order to do that I need to manually refresh by using the Analysis control panel:
This also applies for setting Filters manually in crosstabs. It's not working until you refresh manually.
I hope some of you can tell me if this is the intended way how it should be or if this is due to my lack of programming skills. I need to automate as many of the interactions with analysis as possible because colleagues of mine without any further knowledge of analysis are using these as well.
Thanks a million in advance!