Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
HarshalVakil
Active Contributor
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
12 Comments
Labels in this area