Would like to use a variable instea 1

[Yogi39]

Would like to use a variable insteat of the hard code for the time " '2004-01-24 16:23:06' "criteria in this query.....

With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Excel Files;DBQ=C:\CLOSED.xls;DefaultDir=C:;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("G11"))
        .CommandText = Array( _
        "SELECT `'1$'`.TIME, `'1$'`.B, `'1$'`.C" & Chr(13) & "" & Chr(10) & "FROM `C:\CLOSED`.`'1$'` `'1$'`" & Chr(13) & "" & Chr(10) & "WHERE (`'1$'`.TIME>{ts '2004-01-24 16:23:06'})" & Chr(13) & "" & Chr(10) & "ORDER BY `'1$'`.TIME" _
        )
        .Name = "Query from Excel Files_6"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

 

[IvanMoala]

perhaps ??

[vb]
Dim strDT As String

strDT = "2004-01-24 16:23:06"
'OR
strDT = Format(Now, "yyyy-mm-dd hh:mm:ss&quot

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Excel Files;DBQ=C:\CLOSED.xls;DefaultDir=C:;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("G11&quot)
.CommandText = Array( _
"SELECT `'1$'`.TIME, `'1$'`.B, `'1$'`.C" & Chr(13) & "" & _
Chr(10) & "FROM `C:\CLOSED`.`'1$'` `'1$'`" & Chr(13) & "" & _
Chr(10) & "WHERE (`'1$'`.TIME>{ts '" & strDT & "'})" & _
Chr(13) & "" & Chr(10) & "ORDER BY `'1$'`.TIME&quot

.Name = "Query from Excel Files_6"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
[/vb]


Ivan F Moala

http://www.xcelfiles.com

 

[Yogi39]

thanks the set up you gave me works great...!
However the results are deleting not adding to the range I send to.....xlInsertDeleteCells is this the problem ?