Load Excel files and ensure auto calculation

[danieloberbilli]

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

[ziadm]

Hi

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.

[robert_mika · Accepted solution]

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

[danieloberbilli]

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

Kind Regards

Daniel