Refreshing a query every minutes

[deadseasquirrels]

I have a query shown below:

Sub Daily_Integration()
    Dim connString As String
    Dim dbsNDF As ADODB.Connection
    Dim dbCommand As ADODB.Command
    Dim sqlString As String
    Dim headerRange As Range
    
    If Not Range("A1") = " " Then
        Range("A1").CurrentRegion.ClearFormats
        Range("A1").CurrentRegion.ClearContents
    End If
    
    Range(Cells(1, 1), Cells(1, 4)).Font.Bold = True
    Cells(1, 1) = "DATABASE"
    Cells(1, 2) = "DK_BOX"
    Cells(1, 3) = "Integrated_Thru(Eastern)"
    Cells(1, 4) = "Money Integrated_Thru(Eastern)"
    
    Set dbsNDF = New ADODB.Connection
    Set dbCommand = New ADODB.Command
    
    connString = "Provider=MSDAORA.1;Password=[pwd];User ID=[uid];Data Source=DKARCH1.WORLD;Persist Security Info=True"
    dbsNDF.connectionString = connString
    dbsNDF.Open
                
    sqlString = "select * from so.v_intg_status_all"
                        
    dbCommand.ActiveConnection = dbsNDF
    dbCommand.CommandText = sqlString
    
    Set dbRS = dbCommand.Execute()
    
    Cells(2, 1).CopyFromRecordset dbRS
    Range("A1").CurrentRegion.NumberFormat = "mm/dd/yy h:mm AM/PM"
    
CleanUpAndClose:
    dbRS.Close
    dbsNDF.Close
    Set dbRS = Nothing
    Set dbsNDF = Nothing
    
End Sub

However I would like to set it up so that it runs this query and refreshes the recordset that is returned to cell(2,1) every 5 minutes or so. Is there a way to do this other than looping through the lines below.

dbCommand.ActiveConnection = dbsNDF
dbCommand.CommandText = sqlString
Set dbRS = dbCommand.Execute()
Cells(2, 1).CopyFromRecordset dbRS

Or do I basically have to do a time delay type thing in a loop and just keep on doing it forever? How do I keep time anyway?

 

[Norie]

Is there not an option when you right click the query on the worksheet and select Data Range Properties.

I think it's in the Refresh Control section.

 

[deadseasquirrels]

BUt I mean like building it into the Macro so that it automatically refreshes, is that possible?

 

[Norie]

Why not record a macro when you select the option I talked about?