Use SAP ByDesign Excel Add-in and Macro

[HarshalVakil]

Hello Colleagues,

With this blog post, I would like to explain how we can use SAP ByDesign Excel Add-in in Macro or visual basic. Which would help you to triggers some of the excel add-in feature from macro to automate tasks.

Option Explicit



'Global Variable declarations

Private addin As Object ' the COM object that exposes the ByD addin's functionality

Private newAPI As Boolean ' does the current addin support the "new" API calls?



Private Const SHIPPED_ADDIN_PROGID = "SAPBusinessByDesignExcelAddIn"

Private Const DEBUG_ADDIN_PROGID = "sapExcelAddon"



'Function to find the SAP bydesign Excel Add-in from Installed COM Add-ins

Private Function initialize() As Boolean

    Dim comaddin As comaddin

    For Each comaddin In Application.COMAddIns

        If comaddin.progID = SHIPPED_ADDIN_PROGID And comaddin.Connect Then

            Set addin = comaddin.Object

            'Call your Desired Function

			Dashboard

            initialize = True

            Exit Function

        End If

    Next comaddin



    For Each comaddin In Application.COMAddIns

        If comaddin.progID = DEBUG_ADDIN_PROGID And comaddin.Connect Then

            Set addin = comaddin.Object

            DashBoard

            initialize = True

            Exit Function

        End If

    Next comaddin



    If addin Is Nothing Then

        feedback "addin not found"

        initialize = False

    End If



End Function



'Show the Message box

Sub feedback(text As String)



    Application.StatusBar = text

    'MsgBox text, , "SAP Business ByDesign"

    

End Sub





Sub DashBoard()

	'Determine the SAP ByDesign Excel Addin is installed or not. If already installed, Create object for further process

	initialize

	

	'To refresh the Pivot table after data has been refreshed.

	PivotRefresh

	

	'Show success message once reports and Pivot is refreshed

	feedback ("Success!! All Data has been refreshed in background")

	

	'Switch to the Dashboard Excel Sheet

	Sheets("DashBoard").Activate



End Sub



Private Sub PivotRefresh()

    'To Refresh Specific Pivot table

	Dim pt As PivotTable

	'Select and activate the excel sheet where Pivot table is available

    Sheets("Name of the Excel Sheet where Pivot table is added").Activate

    

	'Select and assign Pivot table to variable

	Set pt = ActiveSheet.PivotTables("Name of the Pivot table")

	

	'to Refresh Single Pivot table

    pt.RefreshTable

	

	

	'To refresh All Pivot tables

	Dim Sheet as WorkSheet, Pivot as PivotTable

	For Each Sheet in ThisWorkbook.WorkSheets

		For Each Pivot in Sheet.PivotTables

			Pivot.RefreshTable

			Pivot.Update

		Next

	Next

	

End Sub



Private Sub DashBoard()



	Dim Reprot1 As Object

    Dim Report2 As Object

    

    On Error Resume Next

'Refresh report 1    

    'Open excel sheet where Report1 is inserted

    Sheets("Sheet1").Activate

	

	'Select the anycell in the report1

    ActiveSheet.Cells("A3").Select

    Set Report1 = addin.FindReport(ActiveCell)

    Report1.Refresh False



'Refresh report 2

	'Open excel sheet where Report2 is inserted

    Sheets("Sheet2").Activate

	

	'Select the anycell in the report1

    ActiveSheet.Cells("A3").Select

    Set Report2 = addin.FindReport(ActiveCell)

    Report2.Refresh False

	

	newAPI = (Err = 0)

    On Error GoTo 0



End Sub

 

This above vb code can be used to refresh the reports using the macro.
If Excel add is not installed, macro will raise the error.

If User has not logged in to the add-in when we try to refresh the report system will prompt user to login first.

In attached file, you can file the some more advance functions as well Such as (read the report parameters,set report parameters etc.)

VB File

Hope this information will help you.
Let me know if there is any feedback/Suggestions.

Regards,
Harshal Vakil