Dynamic Update Macro

Thu, 04 Mar 2010 14:19:20 GMT

Hi There,

I have set up a dynamic update that is working well. However i need the Macro to be run every x Minutes, could I have some suggestions on how best to achieve this?

Many thanks

Gez

Dynamic Update Macro

wizardo — Thu, 04 Mar 2010 14:45:09 GMT

a loop with sleep command

rem ** let QV sleep for 10 seconds **
ActiveDocument.GetApplication.Sleep 10000

Dynamic Update Macro

Mon, 22 Mar 2010 11:07:45 GMT

Thanks for the response but I'm having some problems getting it to work, I've pasted my current macro below, Any help would be much appreciated. Ideally I would like the Macro to re-run every 5 Minutes.

sub PullData()

exit sub

dim sServer, sConn, oConn,oRS,ds, WorkID, QlikSQL, sSQL

sConn="Provider=SQLOLEDB.1;Persist Security Info=False;****************************************************************************************************"
sConn="DRIVER={SQL Server};*******************************************************"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn

set maxbox = ActiveDocument.GetSheetObject("TX06")
WorkID=maxbox.GetText

Set oRS =CreateObject("ADODB.Recordset")
sSQL="Select TOP 10 ID,DateUpdated,UserID,Closed,CompanyID,EmployeeID,Priority from [WORK] WHERE ID>" & WorkID & " ORDER BY ID ASC"
oRS.Open sSQL, oconn,3,2

While (Not oRS.EOF)
QlikSQL = "INSERT INTO QVWork (WorkID,DateUpdated,UserID,Closed,CompanyID,EmployeeID,Priority) VALUES (" & oRS.Fields("ID").Value & ",'" & oRS.Fields("DateUpdated").Value & "'," & oRS.Fields("UserID").Value & "'," & oRS.Fields("Closed").Value & "'," & oRS.Fields("CompanyID").Value & "'," & oRS.Fields("EmployeeID").Value & "'," & oRS.Fields("Priority").Value & ")"

SET Inputting = ActiveDocument.DynamicUpdateCommand (QlikSQL)
oRS.MoveNext
Wend

end sub

sub PullDataDate()

dim sServer, sConn, oConn,oRS,ds, WorkDate, QlikSQL, sSQL
Dim Values

sConn="************************************************************************************************************"
sConn="************************************************************************************************************"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn

set maxbox = ActiveDocument.GetSheetObject("TX07")
WorkDate=maxbox.GetText

Set oRS =CreateObject("ADODB.Recordset")
sSQL="Select ID,DateUpdated,UserID,CAST(Closed AS INT) * - 1 AS Closed,CompanyID,EmployeeID,Priority from [WORK] WHERE DateUpdated>dateadd(minute,-20,'" & WorkDate & "') OR DateCreated>dateadd(minute,-20,'" & WorkDate & "')"
oRS.Open sSQL, oconn,3,2

Values = oRS.GetRows

oRS.close
set sConn=nothing

For r = LBound(Values, 2) To UBound(Values, 2)

SET Inputting = ActiveDocument.DynamicUpdateCommand ("DELETE FROM QVWork WHERE WorkID=" & Values(0, r))
QlikSQL = "INSERT INTO QVWork (WorkID,DateUpdated,UserID,Closed,CompanyID,EmployeeID,Priority) VALUES (" & Values(0, r) & ",'" & Values(1, r) & "'," & Values(2, r) & "," & Values(3, r) & "," & Values(4, r) & "," & Values(5, r) & "," & Values(6, r) & ")"
SET Inputting = ActiveDocument.DynamicUpdateCommand (QlikSQL)

end sub

topic Dynamic Update Macro in QlikView

Dynamic Update Macro

Dynamic Update Macro

Dynamic Update Macro