Disable Calculation in excel

[MarcMorin]

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

[EdGarabedian1]

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

[AndreyKudinov · Accepted solution]

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

[MarcMorin]

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

[NeerajShekhar_R]

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

[AndreyKudinov]

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

[NeerajShekhar_R]

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

[PaulHumphreys]

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