topic RE: Disable Calculation in excel in Product Forum

Disable Calculation in excel

MarcMorin — Tue, 05 Nov 2019 16:24:00 GMT

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

RE: Disable Calculation in excel

EdGarabedian1 — Thu, 07 Nov 2019 12:38:00 GMT

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

RE: Disable Calculation in excel

AndreyKudinov — Thu, 07 Nov 2019 14:02:00 GMT

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

RE: Disable Calculation in excel

MarcMorin — Thu, 07 Nov 2019 19:58:00 GMT

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

RE: Disable Calculation in excel

NeerajShekhar_R — Fri, 08 Nov 2019 06:11:00 GMT

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

RE: Disable Calculation in excel

AndreyKudinov — Fri, 08 Nov 2019 10:30:00 GMT

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

RE: Disable Calculation in excel

NeerajShekhar_R — Fri, 08 Nov 2019 11:02:00 GMT

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

RE: Disable Calculation in excel

PaulHumphreys — Mon, 11 Nov 2019 09:02:00 GMT

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