Skip to Content
Technical Articles
Author's profile photo Harshal Vakil

Use SAP ByDesign Excel Add-in and Macro

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

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Naho Morimoto
      Naho Morimoto

      Dear expert,

      I would like to create a excel macro which enables G/L account - line item report refresh with chosen Account Period.

      I read through this article and tried out the vba code example myself, but I have no crue on how to do it. Please share more detailed explanation on the example code or any assistance is welcome regarding this matter.

       

      Thank you in advance.

      Author's profile photo Harshal Vakil
      Harshal Vakil
      Blog Post Author

      Hello Naho,

       

      Please check the following question on the community for the exact same requirement.

      https://answers.sap.com/questions/11805063/excel-addin-api-how-to-update-a-report-by-a-given.html

       

      let me know if you still need further help.

       

      Thanks.

      Harshal

      Author's profile photo Naho Morimoto
      Naho Morimoto

      Thank you Harshal,

      Problem solved.

      Author's profile photo Chris Ro
      Chris Ro

      Dear Harshal,

       

      Thank you for the guide and post, it is very helpful.

       

      I was wondering if there is a code to refresh ALL reports in a workbook? I currently repurposed the one you wrote, but I have 10+ reports in the workbook and sometimes it seems to skip a report here or there.

       

      Thanks a lot for your help,

       

      Chris

       

      p.s. I tried to de-compile the .dll but was not successful in finding the answer.

      Author's profile photo Venance Rebello
      Venance Rebello

      Hi Harshal,

      How can i automatically login to SAP BYDESIGN Excel Addin.

      Can you please help.

       

      Cheers!

      -ven

       

      Author's profile photo Andrew Muir
      Andrew Muir

      I am also looking for a way to use VBA or something else to auto login into ByDesign Excel Add-in

      If anyone knows or has some ideas please could they share.

      Author's profile photo Chris Ro
      Chris Ro

      I found SSO helped a lot, but not automatically logging in

      Author's profile photo Frederik van Ruijven
      Frederik van Ruijven

      Hi Harshal, the link to the VBA file does not work anymore.

      I can I get access?

      thanks

      Author's profile photo Harshal Vakil
      Harshal Vakil
      Blog Post Author

      Hi Frederik,

      I have uploaded the files again.

      Thanks,

      Harshal

      Author's profile photo Annika Salger
      Annika Salger

      Hello Harshal, thank you for your article. Is there a way to refresh all reports in a workbook at once?

      Thank you for your help.

      Regards, Annika

      Author's profile photo Harshal Vakil
      Harshal Vakil
      Blog Post Author

      Hello Annika,

       

      I don't think that would be possible. Only option would be to loop through the required Excel sheet and refresh the reports one by one.

       

      Another option, I could see is have the automatic refresh when workbooks is opened or worksheet is activated. and from VBA, activating the sheets would do the trick.

       

      Regards,

      Harshal

      Author's profile photo Pritesh Patel
      Pritesh Patel

      Hi Harshal,

       

      I am looking to automate the pulling of just one rep[ort from SAP.  We are looking into using the SAP by design add-in in a VBA but do not know how to automate the logging in by username and password.  any advise would be much appreciated.

       

      Thanks.

       

      -Pritesh.