C
CLR
Hi All........
I have this macro which runs a Query.........It works fine, except the begin
date of 1/1/2006 and the end date of 3/1/2006 are hard-coded within the
macro. I would like to be able to get Begin and End dates from Cells E4 and
E5 respectively. I've tried all sorts of re-configurations of the
DateGroups but am just blundering along and haven't found the right
combination...........if someone would be so kind as to show me how to
change the macro to do this, I would be appreciative.
Sub query2()
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access 97
Database;DBQ=g:\#Train\OldAccessProgram_Keep\Secure
Matrix.mdb;DefaultDir=g:\#Train\OldAccessProgram_Keep;Driv" _
), Array("erId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("A1"))
.Sql = Array( _
"SELECT Level2bucketblank.Name, Level2bucketblank.Process,
Level2bucketblank.Date" & Chr(13) & "" & Chr(10) & "FROM
`g:\#Train\OldAccessProgram_Keep\Secure Matrix`.Level2bucketblank
Level2bucketblank" & Chr(13) & "" & Chr(10) & "WHERE (Level2bucketblank.Dat"
_
, _
"e>={ts '2006-01-01 00:00:00'} And Level2bucketblank.Date<={ts
'2006-03-01 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY
Level2bucketblank.Name" _
)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With
End Sub
TIA
Vaya con Dios,
Chuck, CABGx3
I have this macro which runs a Query.........It works fine, except the begin
date of 1/1/2006 and the end date of 3/1/2006 are hard-coded within the
macro. I would like to be able to get Begin and End dates from Cells E4 and
E5 respectively. I've tried all sorts of re-configurations of the
DateGroups but am just blundering along and haven't found the right
combination...........if someone would be so kind as to show me how to
change the macro to do this, I would be appreciative.
Sub query2()
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access 97
Database;DBQ=g:\#Train\OldAccessProgram_Keep\Secure
Matrix.mdb;DefaultDir=g:\#Train\OldAccessProgram_Keep;Driv" _
), Array("erId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("A1"))
.Sql = Array( _
"SELECT Level2bucketblank.Name, Level2bucketblank.Process,
Level2bucketblank.Date" & Chr(13) & "" & Chr(10) & "FROM
`g:\#Train\OldAccessProgram_Keep\Secure Matrix`.Level2bucketblank
Level2bucketblank" & Chr(13) & "" & Chr(10) & "WHERE (Level2bucketblank.Dat"
_
, _
"e>={ts '2006-01-01 00:00:00'} And Level2bucketblank.Date<={ts
'2006-03-01 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY
Level2bucketblank.Name" _
)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With
End Sub
TIA
Vaya con Dios,
Chuck, CABGx3