VBA for BO - Analysis for Microsoft Excel

Check SAP add-in

Option Explicit
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim iFlag As Integer

Public Sub CheckSAPAddIN_BWInterface(ByRef iFlag As Integer)
    Dim addin As COMAddIn

    '   Run loop for each Add-In in 'COM Add-Ins'
    '   If Addin is related to BW SAP, display Analysis tab
    For Each addin In Application.COMAddIns
        If addin.progID = "SapExcelAddIn" Then
            If addin.Connect = False Then
                iFlag = 1
            End If
        End If
    Next
End Sub

---

Activate SAP add-in

Sub ActivateAddIN_BWInterface()
    Dim lResult As Long
    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
        End If
    Next
End Sub

---

Deactivate SAP add-in

Sub DeActivateAddIN_BWInterface()
    Dim lResult As Long
    Dim addin As COMAddIn

    For Each addin In Application.COMAddIns
        If addin.progID = "SapExcelAddIn" Then
            If addin.Connect = True Then
                addin.Connect = False
            End If
        End If
    Next
End Sub

---

Refreshing BO queries for entire workbook

Public Sub RefreshBO()
'######################################################################################################
'                                                           Refreshing BO Queries for Entire Workbook
'######################################################################################################

Dim wb As Workbook
Dim wsBO As Worksheet, wsInput As Worksheet
    'Checking and Activating SAP AddIns
    Call CheckSAPAddIN_BWInterface(iFlag)
    If iFlag = 1 Then
        Call ActivateAddIN_BWInterface
    End If

    'ReValuing variable to 0
    iFlag = 0

    Set wb = ThisWorkbook

    With wb
        Set wsInput = Sheet14 'Input
        Set wsBO = Sheet5 'BO Query
    End With    

    'xlWkstPivotPeriod.Activate
    Application.StatusBar = "Refreshing BO..."

    'DS_1 Initialization
    Dim Key_Date_DS_1 As String, GL_Account_DS_1 As String, Company_Code_DS_1 As String, Posting_Date_DS_1 As String
    Dim iRet As Long                      'Variable for error code

    '   Set Logon interface to the application
    '   If Error are there, display message
    iRet = Application.Run("SAPLogon", "DS_1", "800", "", "")

    If Val(iRet) <> 1 Then
        MsgBox "Logon failure, Please check username or password", vbOKOnly, "Error"
        iFlag = 1
        Exit Sub
    Else

        'Else refresh the BO data
        'iRet = Application.Run("SAPExecuteCommand", "Refresh")

    End If

    If iFlag = 0 Then
        'wsBO.Calculate
        'Initialize data for different filters in BO query
        With wsInput
            'DS_1
            Key_Date_DS_1 = .Range("E6") '"29.03.2023"                    'Key Date
            Company_Code_DS_1 = "" '.Range("E7") '"2800"                      'Company Code
            Posting_Date_DS_1 = .Range("E9") & "  -  " & .Range("G9") '"01.01.2023  -  31.12.2023" 'Posting Date
            GL_Account_DS_1 = .Range("XEZ3") '                      'GL Account
        End With

        Application.Calculation = xlCalculationManual

        '   Unhide sheet 'BW Data'
        '   Define position from where data would be displayed in tab 'BW Data'
        With wsBO
            .Activate
            .Range("A3").Select

            '   Set filter prompts for DS_1 (data source)
            '   The command will run sucessfully if value 'iRET' returns 1
            Application.StatusBar = "DS_1"
            iRet = Application.Run("SAPExecuteCommand", "Refresh", "DS_1")
            Call Application.Run("SAPSetRefreshBehaviour", "Off")
            Call Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "On")
                iRet = Application.Run("SAPSetVariable", "V_KEYDAT", Key_Date_DS_1, "INPUT_STRING", "DS_1")
                iRet = Application.Run("SAPSetVariable", "V_GLACCOUNT", GL_Account_DS_1, "INPUT_STRING", "DS_1")
                iRet = Application.Run("SAPSetVariable", "AUT01_OM", Company_Code_DS_1, "INPUT_STRING", "DS_1")
                iRet = Application.Run("SAPSetVariable", "V_POSTDAT", Posting_Date_DS_1, "INPUT_STRING", "DS_1")
            Call Application.Run("SAPExecuteCommand", "PauseVariableSubmit", "Off")

        ' ************************************  apply filter  *************************************************
        ' filter 1
         iRet = Application.Run("SAPSetFilter", "DS_1", "0COMP_CODE", "!2017; !2402; !2458; !2502; !2772; !2773; !2820; !2199", "INPUT_STRING")
         ' filter 2
       iRet = Application.Run("SAPSetFilter", "DS_1", "0SOLD_TO__CCUNAME", CU, "INPUT_STRING")
         ' filter 3
       iRet = Application.Run("SAPSetFilter", "DS_1", "0COORDER__CHROPORG", "+" & "31526103" & "(CHRORG)")

            Call Application.Run("SAPSetRefreshBehaviour", "On")

        wb.Save
        End With

    End If

Set wsBO = Nothing
Set wsInput = Nothing
Set wb = Nothing

End Sub