Excel addin problems

[Former Member]

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

[former_member186338]

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.

[Former Member]

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.

[former_member186338]

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!

[Former Member]

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"

[former_member186338]

"it manually by right-clicking the scheduler entry and selecting Run" - insert some log print for vbs code!

[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.

[Former Member]

    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

[former_member186338]

Sorry, but it's not a full code!

[Former Member]

What I posted is all that is relevant to this problem.

[former_member186338]

Sorry, if you want some help let me decide what is relevant! Full code required.

[Former Member]

Well, the full code is over 2,500 lines long, so that's not practical. However, after testing this further, the real failure is the login itself. This login command:

Call EPMObj.Connect("PRD BPC", "ID", "password") 

fails when run from the scheduler, but works when run interactively. VBA's Err.Number is zero when the failure happens.

Is there some setup or permission that might not be set correctly for background logins?

[former_member186338]

Then - full test code!

The minimal code to reproduce the error.

Also for Connect it's better to use full connection string!

[Former Member]

Well, it turns out that the connection process is finicky. EPMObj.Connect can't see/find the connection name when the macro runs from the Windows Task Scheduler. I had to change the connection to use the technical name, like this:

Call EPMObj.Connect("_FPM_BPCNW10_[http://domain.com:28004/sap/bpc/]_[BPC]_[FINANCIAL]_[false]", "ID", "PW")

[former_member186338]

As I told you in my previous reply the format:

_FPM_BPCNW10_[http://domain.com:28004/sap/bpc/]_[BPC]_[FINANCIAL]_[false]

is the recommended format and have to be used!

[former_member186338]

P.S. Now the issue is solved?

[former_member186338]

You are not providing full info.

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