EPMAddInDMAutomation Replacement for 64 bit Excel ...

former_member606476 · ‎03-21-2019

Hi Everyone,

We have a template which uses the EPMAddInDMAutomation and we recently migrated AO 2.7 version.

The template is runnning fine in AO 2.5 version, however the package does not run with the latest version.

After more investigation I found the reason mentioned below from one of the blog linked here :

https://www.column5.com/blog/vba-object-declarations-in-the-epm-plug-in-for-analysis-for-office

“when using the EPM Plug-in with the 64-bit version of Excel, there are certain class methods that are no longer working. Any method in the EPMAddInDMAutomation (note the DM) and the EPMAddInOOAutomation classes of the FPMXLClient library does not work. The only working methods are in the EPMAddInAutomation class. The issue was verified by SAP, but it’s unknown if a fix is being developed for those classes.”

As this blog is from 2017, I was wondering, if there is a replacement of the EPMAddInDMAutomation package by SAP ?

We are using the package to store file in temp folder in local system, upload it to server and delete it.

We get the error, as "File not found", after we increase wait time in one of the part of VBA code, the code runs, we don't get error, but the package also does not run in the server.

Please let us know, if there is a fix to this problem.

Thanks,

Ritesh

former_member186338 · ‎03-26-2019

Universal code to upload file to server will be:

Public Sub SaveFile()

Dim objAddIn As COMAddIn
Dim epm As Object
Dim AOComAdd As Object
Dim blnEPMInstalled As Boolean
Dim strFilePath() As String

For Each objAddIn In Application.COMAddIns
    If objAddIn.progID = "FPMXLClient.Connect" Then
        Set epm = objAddIn.Object
        blnEPMInstalled = True
        Exit For
    ElseIf objAddIn.progID = "SapExcelAddIn" Then
        Set AOComAdd = objAddIn.Object
        Set epm = AOComAdd.GetPlugin("com.sap.epm.FPMXLClient")
        blnEPMInstalled = True
        Exit For
    End If
Next objAddIn

If Not blnEPMInstalled Then
    MsgBox "EPM is not installed!"
    Exit Sub
End If

ReDim strFilePath(0 To 0)
strFilePath(0) = "C:\table.txt"
epm.DataManagerFilesUpload "", "\EXAMPLES\", strFilePath
End Sub

DataManagerFilesUpload - read help: https://help.sap.com/viewer/46f83019d80d4a0787baa7e49f3f95af/2.6.1.0/en-US/93f15bbb21f84e2f90d2a99e9...

Error in help: FilePath is not a string, but array of strings: FilePath() as String

former_member606476 · ‎03-26-2019

Thanks , I will check

former_member186338 · ‎03-26-2019

Please read my blog: https://blogs.sap.com/2017/06/16/simple-vba-function-to-pass-parameters-to-dm-packages/

You can see a sample of using: DataManagerAdvancedRunPackage

former_member606476 · ‎03-26-2019

Hi Please find below the full code .. Please note the problem is with Set oAutomationDM = CreateObject("FPMXLClient.EPMAddInDMAutomation") in code

' the row number which contains the formula used for the property columns
' for employees already existing (default) or not existing
Const rowDefaultFormulae As Integer = 3
Const rowMemberDoNotExistsFormulae As Integer = 4


' row where the first employee could be
Const rowFirstEmployee As Integer = 16


' row where the last employee could be in the excel list
Const rowLast As Integer = 100


' Setting teh column number of every property touched
Const colNN_ID As Integer = 1
Const colNN_Description As Integer = 2
Const colCost_Center As Integer = 3
Const colOrg_Unit As Integer = 4
Const colEmployee_Group As Integer = 5
Const colLocation_Code As Integer = 6
Const colEmployee_Status As Integer = 7
Const colEntry_Date As Integer = 8
Const colApproval_Status As Integer = 9
Const colProfit_Center As Integer = 10
Const colFunctional_Area As Integer = 11
Const colSegment As Integer = 12
Const colCompany_Code As Integer = 13
Const colInternalExternal_Indicator As Integer = 14
Const colPARENTH1 As Integer = 15
Const colMember_Exists As Integer = 16


Const lastCol As Integer = 15
Dim lastRow As Integer
Dim listenedRange As Range








' Determine whether the EPM Add-in is installed
'Relevant if the EMP Version is earlier than 2.3
Function isEpmAddIn()
    Dim obj As COMAddIn
    
    isEpmAddIn = False
    For Each obj In Application.COMAddIns
        If obj.progID = "FPMXLClient.Connect" Then
            isEpmAddIn = True
            Exit For
        End If
    Next
End Function


' Determine whether the Analysis Office is installed
' Relevant if the EPM version is 2.3
Function isAnalysisOffice()
    Dim obj As COMAddIn
    isAnalysisOffice = False
    For Each obj In Application.COMAddIns
        If obj.progID = "SapExcelAddIn" Then
            isAnalysisOffice = True
            Exit For
        End If
    Next
End Function


' Get the version appropriate EPM Reference Object
Function epmInterface() As Object
    Dim cofCom As Object
    If isEpmAddIn() Then
        Set epmInterface = Application.COMAddIns("FPMXLClient.Connect").Object
    ElseIf isAnalysisOffice() Then
        Set cofCom = Application.COMAddIns("SapExcelAddIn").Object
       Set epmInterface = cofCom.GetPlugin("com.sap.epm.FPMXLClient")
    End If
End Function
'End of Code Block to dynamically enable the addin FPXMLClient
'Regardless of EPM Version used
















'Dim listenedRange As Range


Private Sub Worksheet_Activate()
    Set listenedRange = Range("A16:A100")
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Dim changedRange As Range
    Dim cell As Variant
    Dim resultMsgBox As Integer
    Dim newID As String
    Dim rng As Range
    
    
    Set rng = Application.Selection
    
    If rng.Columns.Count > 10000 Then GoTo nochange
    
    Application.ScreenUpdating = False


    ' Range to be listened for any changes
    'Set KeyCells = listenedRange
  Set KeyCells = listenedRange
    
    If KeyCells Is Nothing Then
        Set KeyCells = Range("A16:A100")
    End If
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    
        Me.Unprotect "A123456"
        
        Set changedRange = Range(Target.Address)
        
        For Each cell In changedRange.Rows
        
            If Not IsEmpty(Cells(cell.row, colCost_Center)) Then
                resultMsgBox = MsgBox("Do you want to keep the former Properties of the selected row?", vbYesNo, "Employee ID Changed - Reset Properties")


                If resultMsgBox = vbYes Then
                
                    Set listenedRange = Range("A1:A1")
                    
                    newID = Cells(cell.row, colNN_ID)
                    Application.Undo
                    Range(Cells(cell.row, colNN_Description), Cells(cell.row, colPARENTH1)).Copy
                    Range(Cells(cell.row, colNN_Description), Cells(cell.row, colPARENTH1)).Select
                    Selection.PasteSpecial Paste:=xlPasteValues
                    Cells(cell.row, colNN_ID) = newID
                    
                    Set listenedRange = Range("A16:A100")
                    
                    If Cells(cell.row, colCost_Center) = "SELECT_COST_CENTER" Then
                        Range(Cells(rowMemberDoNotExistsFormulae, colCost_Center), Cells(rowMemberDoNotExistsFormulae, colCost_Center)).Copy
                        Range(Cells(cell.row, colCost_Center), Cells(cell.row, colCost_Center)).Select
                        Selection.PasteSpecial Paste:=xlPasteFormulas
                    End If
                    
                    If Cells(cell.row, colOrg_Unit) = "ORG_UNIT_NONE" Then
                        Range(Cells(rowMemberDoNotExistsFormulae, colOrg_Unit), Cells(rowMemberDoNotExistsFormulae, colOrg_Unit)).Copy
                        Range(Cells(cell.row, colOrg_Unit), Cells(cell.row, colOrg_Unit)).Select
                        Selection.PasteSpecial Paste:=xlPasteFormulas
                    End If
                    
                    Application.CutCopyMode = False
                    Range(Cells(cell.row, colNN_ID), Cells(cell.row, colNN_ID)).Select
                    
                    Application.ScreenUpdating = True
                    Me.Protect "A123456"
                    Exit Sub
                End If
            End If


            If Range("A" & cell.row) = "" Then
                ' when the content of a cell has been deleted, reset this cell (delete properties, lock the row, etc.)
                resetRow cell.row
            Else
                ' when the content contains a (not) existing member, copy the relevant row from above
                If newMemberEnteredExists(cell.row) Then
                    setRowConfigurationMemberExists cell.row
                Else
                    setRowConfigurationMemberNotExists cell.row
                End If
            End If
        
        Next cell
        
nochange:
        
        ' go through the whole list and set filled rows to unlocked and empty rows to locked
        ' as well as format the cell borders to solid lines, in case there were a few lines deleted by the user
        maintainEmployeeList
       
        Me.Protect "A123456"
        
        Application.ScreenUpdating = True
    
    End If
End Sub










Public Sub DeleteSelectedRows()


    Dim selRows As Range
    Set selRows = Application.Selection
    
    Application.ScreenUpdating = False
    
    If selRows.Columns.Count < 10000 Then
        MsgBox "Please select a whole row (click on the row numbers you want to delete)"
        GoTo ende
    End If
    
    Dim lastRow As Integer
    lastRow = selRows.Rows.Count + selRows.row
    
    Me.Unprotect "A123456"


    If selRows.row >= rowFirstEmployee And lastRow - 1 <= rowLast Then
        selRows.EntireRow.Delete
    End If


    Me.Protect "A123456"
    
    Cells(100, 1).Select
    Application.SendKeys ("{F2}")
    Application.SendKeys ("{Enter}")
    Application.SendKeys ("^{HOME}")


    
ende:
    Application.ScreenUpdating = True
    
    End


End Sub




Sub CreateNewMember()




' it could be possible that the created files (xml and csv) couldn't be deleted afterwards.
' then a "permission denied" will be shown. to avoid this, there is a "on error resume next" here
' please replaces this generic "on error" with the particullar permission denied error code
On Error Resume Next


    'Local variable definitions
    Dim xmlResponseFilePath As String
    Dim oPackage As Object
    Dim connUsr As String
    Dim connPwd As String
    Dim teamID As String
    Dim subFolder As String
    Dim sLocalImportFile As String
    Dim sServerImportFile As String
    Dim sTransFile As String
    Dim employeeListCSV As String
    Dim oAutomation As Object
    Dim oAutomationDM As Object
    
    'removed this code again although I have no idea why it works now and not before
    'Using the function in Module 1 to create the Epm variables
    'Set oAutomation = Module1.epmInterface()
    'Set oAutomationDM = Module1.epmInterface()
    
    Set oAutomationDM = CreateObject("FPMXLClient.EPMAddInDMAutomation")
    Set oAutomation = CreateObject("FPMXLClient.EPMAddInAutomation")
    
    
    If Not employeeListIsValid Then
        Exit Sub
    End If
    
    '========================== Begin Declare Variables ==========================
    
    'Admin User and Target Directory for Uploading Files to the Data Manager
    connUsr = ""
    connPwd = ""
    teamID = ""
    subFolder = "Maintain_NN_Employees"
    
    'CSV-FileName for saving locally and finally on the Server
    'employeeListCSV = Evaluate("=EPMUser() & ""_addEmployees_"" & HOUR(NOW()) & MINUTE(NOW())") & ".csv"
    employeeListCSV = Evaluate("=EPMUser()") & "_maintain_NN_Employees.csv"
    sLocalImportFile = Environ("temp") & "\" & employeeListCSV
    
    'Transformationfile on the Server
    sServerImportFile = "\path\" & subFolder & "\" & employeeListCSV
    sTransFile = "\path\FF_LOAD_NNEMPLOYEES_MASTER_TFORM.XLS"
    
    
    '============================ End Declare Variables ==========================
    
    
    
    
    '======================= Creating new Employee beginns =======================
    
    'Create Package for new MasterData
    Set oPackage = getPackageImportMastaData()


    'ConnectWithAdminRights
    'Set oAutomation = getConnectedWithUser(connUsr, connPwd)
    
    'Save the list of masterdata to add to the path defined before
   Call SaveWorksheetsAsCsv(sLocalImportFile, rowFirstEmployee, lastRow, lastCol)


    'Upload the created employee csv file
    File_Upload sLocalImportFile, teamID, subFolder
    
    'Create Local Response XML File
    xmlResponseFilePath = responseXMLFileCreateEmployee(oPackage, sTransFile, sServerImportFile)
    
    'Run Package
    Call oAutomationDM.RunPackage(oPackage, xmlResponseFilePath)
    
    'Wait for 30 seconds until the package ends
    'Application.Wait (Now + TimeValue("0:00:30"))
    
    
    
    '======== cleanup ========
    
    'Delete the local csv File
    SetAttr sLocalImportFile, vbNormal
    Kill sLocalImportFile
    
    'Delete the local created xml file
    SetAttr xmlResponseFilePath, vbNormal
    Kill xmlResponseFilePath
    
    'Close Admin-Connection and Open Connection Manager
    'oAutomation.CloseConnection connStr
    'oAutomation.OpenConnectionManager
    
    '====== end cleanup ======
    
    'Refresh Connection Metadata for new members
    oAutomation.RefreshConnectionMetadata
    '
    'Refresh Workbook
    oAutomation.RefreshActiveSheet
    
    
    
    '========================= Creating new Employee ends =========================




End Sub








Public Sub RefreshSheet()
    
    
    'Using the funtion in module 1 to set the value of the interface
    Dim oAutomation As Object
    Set oAutomation = Module1.epmInterface()


    'Refresh Connection Metadata for new members
    oAutomation.RefreshConnectionMetadata
    '
    'Refresh Workbook
    oAutomation.RefreshActiveSheet


End Sub




Sub copyValidations(rngEMP_GROUP As Range, rngEMP_STATUS As Range, rngAPPROVAL_STATUS As Range, rowLast As Integer)


    Application.ScreenUpdating = False


    Range("listEMP_GROUP").Select
    Selection.Copy
    
    Range(Cells(rngEMP_GROUP.row, rngEMP_GROUP.Column), Cells(rowLast, rngEMP_GROUP.Column)).Select
    Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    
   
   
    Range("listEMP_STATUS").Select
    Selection.Copy


    Range(Cells(rngEMP_STATUS.row, rngEMP_STATUS.Column), Cells(rowLast, rngEMP_STATUS.Column)).Select
    Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False






    Range("list_APPROVAL_STATUS").Select
    Selection.Copy
    
    Range(Cells(rngAPPROVAL_STATUS.row, rngAPPROVAL_STATUS.Column), Cells(rowLast, rngAPPROVAL_STATUS.Column)).Select
    Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False






End Sub








Sub maintainEmployeeList()


    Dim oAutomation As Object
    Set oAutomation = epmInterface()


    'Dim oAutomation As Object
    'Set oAutomation = CreateObject("FPMXLClient.EPMAddInAutomation")
    
  
    Dim row As Integer
    row = rowFirstEmployee
    
    Me.Unprotect "A123456"
    Application.ScreenUpdating = False
    
    
    copyValidations Sheet4.Range(Cells(row, colEmployee_Group), Cells(row, colEmployee_Group)), _
                        Sheet4.Range(Cells(row, colEmployee_Status), Cells(row, colEmployee_Status)), _
                        Sheet4.Range(Cells(row, colApproval_Status), Cells(row, colApproval_Status)), rowLast
    Sheet4.Activate
    Sheet4.Range("A16").Select
    
    Application.ScreenUpdating = False
    
    While row <= rowLast
                
        With Range(Cells(row, colNN_ID), Cells(row, colPARENTH1)).Borders
            .LineStyle = xlContinuous
            .Color = vbBlack
            .Weight = xlThin
        End With
        
        
        Range(Cells(row, colProfit_Center), Cells(row, colPARENTH1)).Interior.ColorIndex = 15
        
     
       
       
        
        If Cells(row, colNN_ID) <> "" Then
            Range(Cells(row, colNN_Description), Cells(row, colApproval_Status)).Locked = False
        Else
            Range(Cells(row, colNN_Description), Cells(row, colMember_Exists)).Locked = True
        End If
        
        
        If Left(Cells(row, colNN_ID), 3) = "NN_" Then
            Range(Cells(row, colEmployee_Group), Cells(row, colApproval_Status)).FormulaHidden = True
        Else
            Range(Cells(row, colEmployee_Group), Cells(row, colApproval_Status)).FormulaHidden = True
        End If
        
        row = row + 1
    Wend
    
    Range("B8").FormulaHidden = False
    Range("B8").Locked = False
    
    Application.ScreenUpdating = False
    Me.Protect "A123456"
    


End Sub


Public Sub addExistingEmployee()


    Dim lastFilledRow As Integer
    lastFilledRow = rowFirstEmployee
    
    Application.ScreenUpdating = False
    
    'Find out which row is the last filled row
    While Cells(lastFilledRow, colNN_ID) <> "" And lastFilledRow <= rowLast
        lastFilledRow = lastFilledRow + 1
    Wend
    
    If lastFilledRow > rowLast Then
        MsgBox "There is no empty row left from row 16 to 100"
    Else
    
        Me.Unprotect "A123456"
    
        If Range("B8") = "SELECT_EMPLOYEE" Then
            MsgBox "Please select a Employee by double clicking on SELECT_EMPLOYEE"
        Else
            Range(Cells(lastFilledRow, colNN_ID), Cells(lastFilledRow, colMember_Exists)).Locked = False
            Cells(lastFilledRow, colNN_ID) = Range("B8")
        End If
        
        Me.Protect "A123456"
        
    End If


End Sub

Function newMemberEnteredExists(row As Integer) As Boolean


    ' return true if the entered employee is existing, which will be checked via EPM-Formula
    ' otherwise return false
    
    Application.ScreenUpdating = False
    
    Cells(rowDefaultFormulae, colMember_Exists).Copy
    Cells(row, colMember_Exists).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas


    Cells(row, colCost_Center).FormulaHidden = True
    Cells(row, colOrg_Unit).FormulaHidden = True
    
    Cells(row, colNN_ID).Select
    
    If Cells(row, colMember_Exists) = 1 Then
        newMemberEnteredExists = True
    Else
        newMemberEnteredExists = False
    End If
    


End Function


Sub setRowConfigurationMemberExists(rowNumber As Integer)

    Range(Cells(rowDefaultFormulae, colNN_Description), Cells(rowDefaultFormulae, colPARENTH1)).Copy
    Range(Cells(rowNumber, colNN_Description), Cells(rowNumber, colPARENTH1)).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Cells(rowNumber, colNN_ID).Select
    
    Application.CutCopyMode = False
    

End Sub

Sub setRowConfigurationMemberNotExists(rowNumber As Integer)
    
    Application.ScreenUpdating = False
    
    Range(Cells(rowMemberDoNotExistsFormulae, colNN_Description), Cells(rowMemberDoNotExistsFormulae, colPARENTH1)).Copy
    Range(Cells(rowNumber, colNN_Description), Cells(rowNumber, colPARENTH1)).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    
    Cells(rowNumber, colCost_Center).FormulaHidden = False
    Cells(rowNumber, colOrg_Unit).FormulaHidden = False
    
    Cells(rowNumber, colNN_ID).Select
    Application.CutCopyMode = False
    


    
End Sub

Sub resetRow(rowNumber As Integer)


    ' resetting a row where the ID has beend deleted


    
    Range(Cells(rowNumber, colNN_Description), Cells(rowNumber, colMember_Exists)).ClearContents
    Cells(rowNumber, colNN_ID).Select
    


End Sub


Function getPackageImportMastaData() As Object


    Const cPackageGroupId As String = "Data Management"
    Dim sPackageId As String
    Dim oPackage As Object
    
    'sPackageId = "Import Master Data Attrib and Hier Flat File"
    sPackageId = "Maintain NN Employee"
    
    Set oPackage = CreateObject("FPMXLClient.ADMPackage")


    'Declare package
    With oPackage
     '.Filename = "/CPMB/IMPORT_MASTER"
     .Filename = "IFI_D_BPC_IMPORT_EMP"
     .GroupId = cPackageGroupId
     .PackageDesc = ""
     .PackageId = sPackageId
     .PackageType = "Process Chain"
     .teamID = ""
     .UserGroup = "0001"
    End With
    
    Set getPackageImportMastaData = oPackage


End Function



Function File_Upload(path As String, teamID As String, subFolder As String)

Dim EPM As Object
Set EPM = Module1.epmInterface()
    Dim FilePath(0) As String
    
    FilePath(0) = path
    EPM.DataManagerFilesUpload teamID, subFolder, FilePath


End Function

former_member606476 · ‎03-26-2019

Hi Vadim,

Thanks for your reply.

The process is :

Some of the data is entered in template, that data is saved in csv file in "_maintain_NN_Employees -The csv file should be saved in temp folder in local and then uploaded to the server.

The process works fine with windows 7 32 bit excel, but it does not work in windows 10, excel 64 bit and AO 2.7 version

Also i found in one of the blogs that

"when using the EPM Plug-in with the 64-bit version of Excel, there are certain class methods that are no longer working. Any method in the EPMAddInDMAutomation (note the DM) and the EPMAddInOOAutomation classes of the FPMXLClient library does not work."

here is the link : https://www.column5.com/blog/vba-object-declarations-in-the-epm-plug-in-for-analysis-for-office

Is there any work around of the problem or any new method introduced by SAP

Regards,

Ritesh

former_member606476 · ‎03-26-2019

  
    Set oAutomationDM = CreateObject("FPMXLClient.EPMAddInDMAutomation")
    Set oAutomation = CreateObject("FPMXLClient.EPMAddInAutomation")
    
    
    If Not employeeListIsValid Then
        Exit Sub
    End If
    
    '========================== Begin Declare Variables ==========================
    
    'Admin User and Target Directory for Uploading Files to the Data Manager
    connUsr = ""
    connPwd = ""
    teamID = ""
    subFolder = "Maintain_NN_Employees"
    
    'CSV-FileName for saving locally and finally on the Server
    'employeeListCSV = Evaluate("=EPMUser() & ""_addEmployees_"" & HOUR(NOW()) & MINUTE(NOW())") & ".csv"
    employeeListCSV = Evaluate("=EPMUser()") & "_maintain_NN_Employees.csv"
    sLocalImportFile = Environ("temp") & "\" & employeeListCSV
    
    'Transformationfile on the Server
    sServerImportFile = "\path\" & subFolder & "\" & employeeListCSV
    sTransFile = "\path\FF_LOAD_NNEMPLOYEES_MASTER_TFORM.XLS"





--Above is the piece of code

EPMAddInDMAutomation Replacement for 64 bit Excel version

Accepted Solutions (0)

Answers (6)

Answers (6)

Iterating through JSONModel with multiple nested a...

Re: Connecting SAP CAP with SAP Cloud ALM

How to use CopyProvider on Table created by TypeSc...

How to execute/fire onPost when user press enter o...

Re: Connecting SAP CAP with SAP Cloud ALM