Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Contributor III

Updating the NOW() value


I have an issue with a table that has Now(1) in a colomne, and the value does not update itself.

I have a macro that uses the valeus of the table to append to excel:

Sub R_AMO_log

set v = ActiveDocument.Variables("ExportDir")

    ExportPath = v.GetContent.String

    ExcelAppend ExportPath &"\Log_regle.xlsx", "CH157" 

End Sub 

Sub ExcelAppend(strExcelAppenFile, strExelAppendObjectID) 

   ' Create an instance of Excel 

   SET objExcelApp = CREATEOBJECT("Excel.Application") 


   ' Open workbook 

   WITH objExcelApp 

      .DefaultSaveFormat = xlWorkbookNormal 

      .DisplayAlerts = FALSE 

      .Workbooks.Open strExcelAppenFile 

      .DisplayFullScreen = FALSE 

      .Visible = FALSE 



   ' Set worksheet 

   SET objExcelSheet = objExcelApp.Worksheets(1) 


   ' Set Excel used range 

   SET objExcelRange = objExcelSheet.Range("A65535").End(-4162) 

   ' Last used row in column A 

   intExcelLastRow = objExcelRange.Row 

   ' Set object to append from 

   SET objObjectFrom = ActiveDocument.GetSheetObject(strExelAppendObjectID) 

   ' Loop all rows of the object except first header row 

   FOR intObjectRow = 1 To objObjectFrom.GetRowCount - 1 

      ' Loop all columns of the object 

      FOR intObjectColumn = 0 To objObjectFrom.GetColumnCount - 1 

         ' Get object data 

         SET objCell = objObjectFrom.GetCell(intObjectRow, intObjectColumn) 

         ' Add that data to Excel cell 

         objExcelSheet.Cells(intObjectRow + intExcelLastRow, intObjectColumn + 1) = objCell.Text 




   ' Save and quit 

   objExcelSheet.SaveAs strExcelAppenFile 


   SET objExcelSheet = NOTHING       

   SET objExcelApp = NOTHING 



The Now() seems to randomly update itself when I run the macro. Sometimes it does, sometime it doesn't...

Do you know what triggers the update of that value?

2 Replies
Contributor II

Re: Updating the NOW() value

You could try creating a variable:

let vNow = '=Now()';

Note the single quotes.

In your column, instead of using Now(), use ='$(=vNow)'

I tried this in a sample document with a text field and every time I touch the text box with a mouse, it updates the time.

Contributor III

Re: Updating the NOW() value

That works a little too well.

Updates itself without activating the object...

Community Browser