Problems using EPM functions via VBA

[former_member414993]

Hello, experts!

I am trying to call EPM function EPMSaveComment in VBA using Application.Run Method. This code launchs on event, when user tries to change text in textbox. It works not stable, very often it returns value "#RFR", but sometimes it works ok and returns correct comment. I need to read this comment, not to save, but functions EPMCommentPartialContext or EPMCommentFullContext do not work at all returning error "#Error - unexpected error. Check parameters" so i was forced to try EPMSavecomment instead. Code example above.

My software versions:

BPC 10.0 NW

EPM SP22 NET3.5 9188

Also tried on EPM SP34 Patch 2. NET 4.7.2 x86 10361 - facing the same problem.

[former_member186338]

Sorry, but if you want to post code - there is a special "CODE" button...

By the way, what is the idea behind reading comment in VBA?

[former_member186338]

After simple tests I see that Application.Run("EPMSaveComment"...

will provide a correct result only when:

1. There is the cell on the workbook with EPMSaveComment for the required intersection

2. Worksheet is refreshed.

[wf]

Ivanov,

Not very clear yet what you are doing, but the #RFR is a rather common issue working with EPM retrieve/send formulas. I am not sure there is a logic to it but basically it seems to happens when the excel sheet is being calculated without refreshing the EPM report.

The workaround for me is to add with your Code "RefreshSelectedCells" targeting the specific range with your EPM formulas to ensure they are refreshed at the right moment.

Below a basic example using the After refresh where it is used to refresh a few columns having EPMretrieve formulas:

Function AFTER_REFRESH()
Dim EPM As Object

Dim RngReport As Range
Dim ws As Worksheet
Dim EndReport As String

'Define EPM report range
Worksheets("INPUT").Activate
    Set ws = ActiveSheet
    EndReport = EPM.GetDataBottomRightCell(ws, "000")
Set RngReport = Range(Range("Start_Pos").Offset(1, 0), EndReport)

'Refresh columns 5 to 10
Range(RngReport.Columns(5), RngReport.Columns(10)).Select
EPM.RefreshSelectedCells

Range("Start_Pos").Select

End Function

[former_member186338]

Strange logic to my mind!

RefreshSelectedCells

in AFTER_REFRESH event function...

Double refresh as a result.

And also AFTER_REFRESH will be triggered after any refresh of any report ...

[wf]

I agree, but here the point is just to show how to get rid of the #RFR. The wider context of this example is that the after refresh is used to overcome a loss of functionality that came with the introduction of EPM reports. The "logical" solution would be to use local members but I prefer a negligible loss in performance with this extra refresh over a considerable change effort as editing reports having a certain amount of local members is an incredible tedious affair.

[former_member186338]

Still unable to completely understand the logic...

EPMSaveComment will show #RFR when:

1. list of dimension members in EPMSaveComment parameters was changed

2. Cell containing EPMSaveComment was not refreshed (using Refresh Worksheet/Workbook or RefreshSelectedCells or if EPMSaveComment is in local member - Refresh report)

Also results of EPMSaveComment are cached duiring single session.

[former_member186338]

The idea is bad in general:

1. Comments are not edited - new version of comment is appended to the table after each save,

2. You can try to implement badi BADI_UJC_COMMENT_PREPROCESS

https://launchpad.support.sap.com/#/notes/0002002431

https://launchpad.support.sap.com/#/notes/0002424484

you will see the user saving comment...

[former_member414993]

1. U'r absolutely right, but why should this fact interfere? I've already implement this mechanism in one input form and it works fine. There was no such problems in that input form, because i have cells with exact EPMsaveCommnet or EPMFullcontext formulas in it which refreshes in standart way. But in input form, which i work on now, i would love to read this comments via VBA, with no formulas in cells in fact and at event of changing text box, not if refresh time. So i wonder if it is possible with no having #rfr issues...?

2. I am trying to avoid abap programming here if it is possible to do in other more transparent for support way.

[former_member186338]

alexiv

Even with EPMSaveComment in the cells sometimes you will have #RFR! Unfortunately there is no reliable way to read comment using VBA.

In this case - if you want to have a reliable solution - then use ABAP.

[former_member414993]

I use screenshoot just to show the watches, that everything defined. Thx for advice, the code is:

tech_com_text = Application.Run("EPMSaveComment", Cells(cl_collection(1).Row, 1).Value, "ZZZZZ", "STATUS_CUR_USER1", "U_NONE", "WFR_NONE", cl_collection(1).CFR_ID, Range("TIME_CONTEXT").Value, Range("CAT_CONTEXT").Value, cl_collection(1).ID, "PERIODIC")
com_text = Application.Run("EPMCommentFullContext", "ZZZZZ", "STATUS_CUR_STATUS", "U_NONE", "WFR_NONE", cl_collection(1).CFR_ID, Range("TIME_CONTEXT").Value, Range("CAT_CONTEXT").Value, cl_collection(1).ID, "PERIODIC")

The idea is to restrict editing of comments depending on your role and login. Comment should be able for editing just for an author of comment or someone with higher role level. So when user post a comment, the same moment we post technical comment with his login and role. When someone will try to edit this comment, we need to check login of current user and login in tech com and if it is not the same we check does he have a higher role comparing with that role in technical comment.

[former_member186338]

In general, EPM comment related functions are not reliable, even when used on the worksheet - not in vba.