Topic Thread

Expand all | Collapse all

Disable Calculation in excel

Jump to Best Answer
  • 1.  Disable Calculation in excel

    Posted 11-05-2019 16:24
    HI is there a way to disable calculations in excel

    basically the equivalent of 'Application.Calculation = xlManual'

    please help



    ------------------------------
    Marc Morin
    Consultant
    PWC
    America/New_York
    ------------------------------


  • 2.  RE: Disable Calculation in excel

    Posted 11-07-2019 12:38
    Hi Marc,  could you provide more detail on how you are trying to use 'Application.Calculation = xlManual' in your BP process?  Doing some searches it looks like 'Application.Calculation = xlManual' is typically added via a coded excel macro.  Perhaps you could create the macro in your target excel and then use the MS Excel VBO action "Run Macro" to execute the macro containing the 'Application.Calculation = xlManual' statement.



    ------------------------------
    Ed Garabedian
    Sr. Solutions Consultant
    Blue Prism
    ------------------------------



  • 3.  RE: Disable Calculation in excel
    Best Answer

    Posted 11-07-2019 14:02
    Code would be like this: 
    Dim xlInstance = GetInstance(handle)
    xlInstance.Calculation = -4135 'xlManual
    xlInstance.Calculation = -4105 'xlAutomatic

    ------------------------------
    Andrey Kudinov
    Project Manager
    MobileTelesystems PJSC
    Europe/Moscow
    ------------------------------



  • 4.  RE: Disable Calculation in excel

    Posted 11-07-2019 19:58
    That did the trick, do you have any idea why it work with '-4135 and not using 'xlManual'?


    also it is alot less 'overkill' then my workaround which included adding Microsoft.Office.Interop.Excel dll
    "
    Dim wb As Object
    Dim excel as Object

    wb = GetWorkbook(Handle, workbookname)
    excel = wb.Application
    excel.Calculation = -Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual
    "
    Again big thanks
    Marc



    ------------------------------
    Marc Morin
    Consultant
    PWC
    America/New_York
    ------------------------------



  • 5.  RE: Disable Calculation in excel

    Posted 11-08-2019 06:11
    Hi All,
    There is an action in ms excel vbo.
    "Set calculation mode" where you can actually set it to manual or automatic.

    ------------------------------
    Neeraj Shekhar Rajput
    Developer
    Genpact India
    Asia/Kolkata
    ------------------------------



  • 6.  RE: Disable Calculation in excel

    Posted 11-08-2019 10:30
    ​> That did the trick, do you have any idea why it work with '-4135 and not using 'xlManual'?
    That is because xlManual is not defined. None of the enums are. Using Microsoft.Office.Interop.Excel dll is an option, but adding it just for enums is a bit of an overkill. You can define them in global if you want code to be cleaner.

    > "Set calculation mode" where you can actually set it to manual or automatic.
    Not in an older VBO , didn't check newer ones.  


    ------------------------------
    Andrey Kudinov
    Project Manager
    MobileTelesystems PJSC
    Europe/Moscow
    ------------------------------



  • 7.  RE: Disable Calculation in excel

    Posted 11-08-2019 11:02
    Hi Andrey,

    It was in new one.
    There are two input one for handle and other for to enable the automatic cal mode.

    if not PFB.
    Dim ExcelApp as Object = GetInstance(handle)
    Dim ExcelApp as Object = GetInstance(handle)
    Dim mode as Integer
    If enabled Then mode = -4105 ' xlCalculationAutomatic
    Else mode = -4135 ' xlCalculationManual
    End If
    ExcelApp.Calculation = mode

    ------------------------------
    Neeraj Shekhar Rajput
    Developer
    Genpact India
    Asia/Kolkata
    ------------------------------



  • 8.  RE: Disable Calculation in excel

    Posted 11-11-2019 09:02
    ​To try and answer your specific question as to why xlCalculationManual needs to be substituted with -4135, the reason is as follows:

    The dynamic link library (DLL) that allows the use of the descriptor rather than the value is not available in Blue Prism.  If you ever come across anything else of this mature that you need to devise a code stage for, you can open Excel and go into the Visual Basic Editor (Alt+F11 is a handy shortcut for doing this).  In the VB editor you can access the Object Browser in the View menu ( or by pressing F2).

    In here there is a search functionality where you can enter the specific descriptor you want to look up, e.g. xlCalculationManual, and it will tell you what the numerical value equivalent is.  I have written quite a few code stages by looking up in this way previously.

    Hope this helps further.

    Paul

    ------------------------------
    Paul Humphreys
    Senior Technical Specialist
    Europe/London
    ------------------------------