Updating the NOW() value

sibrulotte — Wed, 22 Oct 2014 15:45:37 GMT

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

' 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?

Re: Updating the NOW() value

Anonymous — Thu, 23 Oct 2014 18:55:57 GMT

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.

Re: Updating the NOW() value

sibrulotte — Tue, 28 Oct 2014 20:37:32 GMT

That works a little too well.

Updates itself without activating the object...

topic Re: Updating the NOW() value in QlikView

Updating the NOW() value

Re: Updating the NOW() value

Re: Updating the NOW() value