EPM Template has intermittent Visual Basic Run-time error

[Former Member]

Hi, I have an EPM template with some macros. I intermittently get the below Visual Basic Run-time error -1073479167 (c0040201)' when I refresh the template. Sometimes it refreshes fine, other times I get the error (without changing anything). The strange thing is that the exact same macros/code are in several other templates which don't produce this error. Do you have any idea why this error might be occurring? Could it be related to something in the system outside of the template/macros?

Thank you for any help!

[gajendra_moond]

Hi Lin

Please share your code for refresh. Also, is your sheet protected? Make sure that you have provided the same password in protection tab in sheet options as well.

[Former Member]

We have similar issue with a run time error - '1073479167 (c0040201)':

Error in ClearAllCachesToGet - Excel

VB code is as follows

Sub Refresh()

Application.ScreenUpdating = False

Application.EnableEvents = False

        Sheets("sheet1").Select

        EPMobject.RefreshActiveSheet

           Sheets("sheet2").Select

        EPMobject.RefreshActiveSheet

             Sheets("sheet3").Select

        EPMobject.RefreshActiveSheet

              Sheets("sheet4").Select

        EPMobject.RefreshActiveSheet

               Sheets("Source").Select

Application.ScreenUpdating = True

Application.EnableEvents = True

End Sub

It usually fails in the first "refreshactivesheet" and all the EPM sheets lose the connection right afterwards.

EPM will say it is still logged on but the EPM tabs will look like all icons greyed out.

Appreciate your help on this.

Thanks

[former_member186338]

Try to test without:

Application.ScreenUpdating = False

Application.EnableEvents = False

Vadim

[Former Member]

Hi Vadim,

I commented out the lines as above and it seem to work on my excel. But with a different user I get

compile error: Method or Data member not found

FYI, the other user runs EPM 10 SP13 .NET 3.5 and I am on SP17 .NET 4.0

If I uncomment the same once I run once successfully, it doesn't give me the earlier error (Error in ClearAllCachesToGet - Excel) So I really cant tell if these lines created the issue.

Its very inconsistent and also sometime doesn't refresh sheets properly.

Lin,

Sorry to hijack your thread. We also experience very inconsistent results and errors.

Thanks

[gajendra_moond]

Hi Mani

SP levels are quite old. The following code works perfectly for me to refresh the sheet.

Sub Refresh()

Dim EPMobject As New FPMXLClient.EPMAddInAutomation

Application.ScreenUpdating = False

Application.EnableEvents = False

ActiveSheet.Unprotect Password:="your password"

EPMobject.Refresh

Application.ScreenUpdating = True

Application.EnableEvents = True

ActiveSheet.Protect Password:="your password", AllowFormattingColumns:=True, _

AllowFiltering:=True

End Sub

The inconsistent behavior does depend on the client machine configuration as well.

[former_member186338]

I can't help you with "EPM 10 SP13 .NET 3.5 and I am on SP17 .NET 4.0"

Vadim

[damovand]

Hello,

I write some VBA code for my personal use.  I think when the code depends on retrieving information from an existing object, e.g. a specific cell value from a worksheet from a workbook, and the value or object is not valid then VBA fails.  In my case, for example, my macros fail if I have another workbook already open because I look for specific worksheets from a workbook. 

You can prevent the error by catching the error if the worksheet does not exist or doesn't contain the information. 

Regards,

Leila

[former_member186338]

Hi Lin,

Without looking on the VBA code in the template - nothing to discuss

Vadim

[former_member190501]

Hi,

Did you try

                         1729697  - EPM Add-in AddMemberToPageAxis methods returns "run-time error '-1073479167 (c0040201)' E...

Thanks,

Raju