Skip to Content
0
Jan 16, 2020 at 09:35 AM

Formulas not working correctly after Data Refresh with VBA for Analysis for Excel

169 Views

Hello,

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!

Felix

Attachments

example1.png (4.3 kB)
control-panel.png (23.2 kB)