Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

Load Excel files and ensure auto calculation

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

1 Solution

Accepted Solutions
Highlighted

Re: Load Excel files and ensure auto calculation

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

View solution in original post

3 Replies
Highlighted
Specialist
Specialist

Re: Load Excel files and ensure auto calculation

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

Re: Load Excel files and ensure auto calculation

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

View solution in original post

Highlighted
Specialist II
Specialist II

Re: Load Excel files and ensure auto calculation

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

Kind Regards

Daniel