Skip to Content
avatar image
Former Member

Excel addin problems

I had an Excel automation macro in 32bit EPM 10 that worked fine. I used a .VBS script to launch my macro via the Win7 job scheduler. That macro opens a template EPM report, uses SetContextOptions to change the Profit Center and Time, uses RefreshActiveWorkBook to refresh the report, and then uses SaveAs to save it. That process repeats for 45 Profit Centers. Again, that worked fine in EPM 10.

After migrating to 64bit Analysis for Office/EPM on a 64bit Win10 virtual machine, that process behaves oddly. When the macro tries to use SetContextOptions while it is running from the job scheduler, it fails. Even though the failure triggers the On Error command, there is no error code - Err.Number is zero, and Err.Description is blank. The job scheduler entries are the same in both environments. Both are set to "Run only when user is logged in". Also, the failure happens both when the scheduler triggers the macro and when I trigger it manually by right-clicking the scheduler entry and selecting Run.

If I open the VBA project, and manually start the macro with F5, the code works normally. Why would SetContextOptions behave differently in batch mode vs. interactive?

Here is the command that I'm using:

EPMObj.SetContextOptions wb, "PRD BPC", "TIME", strPeriod, False

Thanks

Tom

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Aug 02, 2017 at 06:04 PM

    You are not providing full info.

    Please post the text of VBS script and the macro code in Excel.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 02, 2017 at 07:33 PM
        Dim EPMObj           As Object
        Dim addin           As Object
        Set addin = Application.COMAddIns("SapExcelAddIn").Object
        Call addin.ActivatePlugin("com.sap.epm.FPMXLClient")
        Set EPMObj = addin.getplugin("com.sap.epm.FPMXLClient")
        Call EPMObj.Connect("PRD BPC", "ID", "password")
        EPMObj.SetContextOptions wb, "PRD BPC", "TIME", strPeriod, False
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 02, 2017 at 07:37 PM

    Here is the VBS script. It copies the macro from the network to a local folder, and then runs the macro from the local folder.

    Set xl = WScript.CreateObject("Excel.application")
    xl.Application.Visible = False 
    Set fso = WScript.CreateObject("Scripting.FileSystemObject")
    if fso.FileExists("C:\Logs\Temp\Financial Report Automation.xlsm") then
    fso.DeleteFile "C:\Logs\Temp\Financial Report Automation.xlsm", True
    end if
    fso.CopyFile "M:\Financial Report\Financial Report Automation.xlsm", "C:\Logs\Temp\Financial Report Automation.xlsm", True
    Set xlBook = xl.Application.Workbooks.Open("C:\Logs\Temp\Financial Report Automation.xlsm")
    xl.Application.run "'" & xlBook.Name & "'!RunFirstInstanceManually"
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      The VBS launches the macro fine. I have extensive logging in the macro that confirms where the error happens - SetContextOptions triggers the On Error process, but Err.Number is zero.

  • avatar image
    Former Member
    Sep 01, 2017 at 06:57 PM

    No, it's not resolved. Now it fails to even log in. The code below fails to connect when started from the Windows Task Scheduler, but works normally when started manually with F5 from within the macro editor.

    Dim EPMObj     As FPMXLClient.EPMAddInAutomation
    Dim myEPM      As Object
    Dim addin      As Object
    Set addin = Application.COMAddIns("SapExcelAddIn").Object
    Call addin.ActivatePlugin("com.sap.epm.FPMXLClient")
    Set EPMObj = addin.getplugin("com.sap.epm.FPMXLClient")
    Call EPMObj.Connect("_FPM_BPCNW10_[http://sappb1.mydomain.com:28004/sap/bpc/]_[BPC]_[FINANCIAL]_[false]", "ID", "PW")

    Just like before, the failure triggers the On Error process, but Err.Number is zero and Err.Description is empty.

    Add comment
    10|10000 characters needed characters exceeded

    • For testing purpose try to setup scheduled distribution of some template to some folder (EPM distribution function).

      This feature is using the same Windows tack scheduler. Look on the schedule item setup!

  • Sep 01, 2017 at 07:43 PM

    Or look on my sample code to create a correct task schedule item using VBA (EPM is doing the same internally):

    Option Explicit
    
    Sub SetTask()
    '------------------------------------------------------------------
    ' This sample schedules a task to start FPMXLClient.BooksPublication.exe 60 seconds
    ' from the time the task is registered.
    '------------------------------------------------------------------
    
    ' A constant that specifies a time-based trigger.
    Const TriggerTypeTime = 1
    ' A constant that specifies an executable action.
    Const ActionTypeExec = 0
    
    Dim service
    '********************************************************
    ' Create the TaskService object.
    Set service = CreateObject("Schedule.Service")
    Call service.Connect
    
    '********************************************************
    ' Get a folder to create a task definition in.
    Dim rootFolder
    Set rootFolder = service.GetFolder("\")
    
    ' The taskDefinition variable is the TaskDefinition object.
    Dim taskDefinition
    ' The flags parameter is 0 because it is not supported.
    Set taskDefinition = service.NewTask(0)
    
    '********************************************************
    ' Define information about the task.
    
    ' Set the registration info for the task by creating the RegistrationInfo object.
    Dim regInfo
    Set regInfo = taskDefinition.RegistrationInfo
    regInfo.Description = "BPC Distribution"
    regInfo.Author = "VEKHOME01\KalininVE"
    
    '********************************************************
    ' Set the principal for the task
    Dim principal
    Set principal = taskDefinition.principal
    
    ' Set the logon type to 6 - TASK_LOGON_INTERACTIVE_TOKEN_OR_PASSWORD
    principal.LogonType = 6
    
    
    ' Set the task setting info for the Task Scheduler by
    ' creating a TaskSettings object.
    Dim settings
    Set settings = taskDefinition.settings
    settings.Compatibility = 1
    settings.DisallowStartIfOnBatteries = False
    settings.StopIfGoingOnBatteries = False
    settings.Enabled = True
    settings.StartWhenAvailable = True
    settings.Hidden = False
    settings.Priority = 5
    Dim idlesettings
    Set idlesettings = settings.idlesettings
    idlesettings.StopOnIdleEnd = False
    idlesettings.RestartOnIdle = False
    
    '********************************************************
    ' Create a time-based trigger.
    Dim triggers
    Set triggers = taskDefinition.triggers
    
    Dim trigger
    Set trigger = triggers.Create(TriggerTypeTime)
    
    ' Trigger variables that define when the trigger is active.
    Dim startTime, endTime As Variant
    
    Dim time
    time = DateAdd("s", 60, Now)  'start time = 60 seconds from now
    ' startTime format: YYYY-MM-DDTHH:MM:SS
    startTime = Format(time, "yyyy-mm-ddThh\:nn\:ss")
    
    Debug.Print "startTime :" & startTime
    
    trigger.StartBoundary = startTime
    trigger.ID = "TimeTriggerId"
    trigger.Enabled = True
    
    '***********************************************************
    ' Create the action for the task to execute.
    
    ' Add an action to the task to run notepad.exe.
    Dim Action
    Set Action = taskDefinition.Actions.Create(ActionTypeExec)
    Action.Path = "C:\Program Files (x86)\SAP BusinessObjects\EPM Add-In\FPMXLClient.BooksPublication.exe"
    Action.Arguments = """C:\Users\KalininVE\Documents\PC_NW\KalininVE\Books\ENVNAMEXXX\PLANNING\WebExcel\BatchFiles\D XX.X.XX.XX_8000 ENVNAMEXXX PLANNING KalininVE"""
    
    Debug.Print "Task definition created. About to submit the task..."
    
    '***********************************************************
    ' Register (create) the task.
    'Task Name:"AAAAAAAAAAAAAAAAAAAAAA"; taskDefinition; Flags: 0x6 - TASK_CREATE_OR_UPDATE; WindowsUsername; Password; logonType: 6 - TASK_LOGON_INTERACTIVE_TOKEN_OR_PASSWORD
    
    Call rootFolder.RegisterTaskDefinition("AAAAAAAAAAAAAAAAAAAAAA", taskDefinition, 6, "VEKHOME01\KalininVE", "PASSWORDXXX", 6)
    Debug.Print "Task submitted."
    
    End Sub

    Correction: Time conversion function is replaced by format() to simplify code.

    Add comment
    10|10000 characters needed characters exceeded