Input = Sheets(“Your sheet”).Cells(, 2).Valueįinally you can use this Input to fill another variable in the second datasource, for example. =SAPListOfVariables(“DS_1″ ”KEY” ”PROMPTS”)Īfterwards, you have to read the value of the respective cell with the right variable content:
That means if a user filled some variables in the prompt screen, you have to define an area in your workbook where you can get the list of all the variables, by using the formula: One more function is missing in my opinion… There is no SAPGetVariable() available. LResult = Application.Run(“SAPExecuteCommand”, “ShowPrompts”, “DS_1”) LResult = Application.Run(“SAPSetVariable”, “”, “”, “KEY”, “DS_1”) LResult = Application.Run(“SAPExecuteCommand”, “Refresh”, “DS_1”) Unfortunately I did not found a “clear” method to remove the variable contents again.įor that reason I helped myself by setting one “default” value as key, which exists in the master data, and afterwards the user is able to simply remove it in the variable input screen: Maybe you have the requirement to handover a long list of input characteristics to a query variable (like you would do it via the “copy from clipboard”-button), and refresh it. Step 2: In your VBA module you put all your coding between these lines:Īttention: This coding is executed almost every time – even if you don’t really do anything, but just click “save”.įor that reason, you could read this note – there is a hint about improvements in your coding: you can register a callback:Ĭall Application.Run(“SAPExecuteCommand”, “RegisterCallback”, “AfterRedisplay”, “Callback_AfterRedisplay”) by drill-down, execution of Variable prompt, etc. When you want a macro to execute everytime your datasource has been refreshed, e.g. In that way every blank header cell receives the text of the previous one including an attached “2”.Ħ) Actions after the Refresh of a Datasource: Register Callback LResult = Application.Run(“SAPGetProperty”, “IsDataSourceActive”, “DS_1”)ĭS1_C = Range(“SAPCrosstab1”).Columns.Count Nothing more is annoying – when you want to create a PIVOT table on top of your datasource’s results – than the missing feature from the very first release: No headers on characteristics with “Key and text” displayįor preventing problems 3) you should execute the macro only after 1): I prefer using:ĥ) Fill the missing table headers for “Key and text” characteristics
#How to execute a sas program through excel vba on windows update#
To prevent a user is bothered with jumping through several thousand lines or several sheets, you always should freeze the screen before VBA execution and activate the screen update afterwards again. Therefore I try to avoid the checkbox “Remove Data Before Saving”. Then the crosstab is not existing and will cause you trouble if you execute a macro during Workbook initialization. So if you need reliable variable contents you should store the previous “dimensions” of the crosstab in any cells on the sheet.Įspecially if you “Remove Data Before Saving”. LCols = Range(“SAPCrosstab1”).Columns.Countįor this, also 1) applies! If your Datasource is not refreshed, you will not get the correct result. To find out the last column and row of your crosstab, you can use: Of course you might need to adjust the formulas if they rely on certain key figure columns, but for “VLookups” it quite often saves work. In that way you don’t have to take care about removing the columns when any drill-downs are happening and replacing them afterwards at the new end of the report. If you have the task to include your own Excel formula columns in your sheet, then always try to aviod putting it at the end of the Data source! Instead, put it at the beginning: LResult = Application.Run(“SAPExecuteCommand”, “ ShowPrompts“, “DS_1”) LResult = Application.Run(“SAPExecuteCommand”, “ Refresh“, “DS_1”) LResult = Application.Run(“SAPGetProperty”, “ IsDataSourceActive“, “DS_1”) To prevent a refresh every time, you can check if the DS is already active The most important thing when doing anything in VBA related to Analysis is to refresh your datasource (DS) first … otherwise, nothing will work!
It might be that Analysis behaviour already changed in 2.x versions.
This document does not go too much into detail, since my idea is that also beginners of VBA in Analysis should be able to utilize some tips.įYI: I worked only in 1.4.x versions so far. Over the last year, I created some small applications in Analysis and thought it’s time to collect a few best practice ideas, which I experienced during development and test.