Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

sibrulotte
Contributor III

Updating the NOW() value

Hi,

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 

   END WITH 

    

   ' 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 

      NEXT 

   NEXT 

    

   ' Save and quit 

   objExcelSheet.SaveAs strExcelAppenFile 

   objExcelApp.Application.Quit 

   SET objExcelSheet = NOTHING       

   SET objExcelApp = NOTHING 

  

END SUB 

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

sibrulotte
Contributor III

Re: Updating the NOW() value

That works a little too well.

Updates itself without activating the object...

Community Browser