topic Re: Load Excel files and ensure auto calculation in QlikView

Load Excel files and ensure auto calculation

danieloberbilli — Thu, 09 Apr 2015 01:28:18 GMT

Dear All,

We are facing the problem, that some input Excel files were edited by the user but saved without processing the calculation (auto calculation was turned off). I am aware that there are Excel macros to control auto calculation, but this is not an option at the moment. I guess there is no way to tell Qlik to force execution of calculation when reading the Excel file...but is it possible to check whether the file has auto cal activated or not? Like an Excel-cell containing a function that returns a true/false for this status what I could read in Qlik?

Kind Regards

Daniel

Re: Load Excel files and ensure auto calculation

ziadm — Thu, 09 Apr 2015 04:32:18 GMT

I would advise having the below macro in the excel sheet

Private Sub Workbook_Close()

If Application.Calculation = -4135 Then

Dim Response As VbMsgBoxResult

Response = MsgBox("Calculation is set to MANUAL, This is not Recommended" & vbCrLf & "Set calculation to automatic?", vbYesNo + vbCritical, "Excel Calculation Check")

Select Case Response

Case vbYes

Application.Calculation = xlCalculationAutomatic

MsgBox ("Calculation has been set to AUTOMATIC", vbOKOnly + vbInformation, "Excel Calculation Check")

Case vbNo

MsgBox ("Calculation has left as MANUAL", vbOKOnly + vbInformation, "Excel Calculation Check")

End Select

End If

End Sub

This should force Auto Calculation Auto on the close event.

Re: Load Excel files and ensure auto calculation

robert_mika — Thu, 09 Apr 2015 08:26:08 GMT

You can not save file without recalculation even if the Manual calculation are ON as pressing Save trigger recalculations.

Manual/Automatic recalculation are Application base not file.

But if you have your calculation set to manual you can use below macro to change to automatic

In QV press Ctrl+M

Paste this code:

Sub Excel_ChangeToAuto

'Open Excel

set XLApp = CreateObject("Excel.Application")

'Open Workbook

set XLDOC = XLApp.Workbooks.Open ("c:\Users\ ...here enter full path to your spreadsheet

XLApp.Visible = True

'Send Key to set Calculations

XLApp.SendKeys "%m+x+a", True

End Sub

Re: Load Excel files and ensure auto calculation

danieloberbilli — Wed, 15 Apr 2015 06:17:44 GMT

Thanks for your help, Robert. I will try it out soon.

Kind Regards

Daniel