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)
Next
end sub
