Thread: Date from a cell........

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

usually you can just stop with " & range("a1") & " continue

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"CLR" <croberts@tampabay.rr.com> wrote in message
news:uEbqtFmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> 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
>
>
>
>

Thanks Don........but I still can't get there from here........I've tried
all sorts of combinations like that........

Vaya con Dios,
Chuck, CABGx3


"Don Guillett" <dguillett1@austin.rr.com> wrote in message
news:uW5UIbmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> usually you can just stop with " & range("a1") & " continue
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "CLR" <croberts@tampabay.rr.com> wrote in message
> news:uEbqtFmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > 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
> >
> >
> >
> >
>
>

This may have a space problem or two, so check it closely, but it should be
close:

Sub query2()
Dim DT1 As String, DT2 as String

DT1 = Format(Range("B9").Value, _
"YYYY-MM-DD 00:00:00")

DT2 = Format(Range("B10").Value, _
"YYYY-MM-DD 00:00:00")

With ActiveSheet.QueryTables.Add(Connection:= _
Array(Array("ODBC;DSN=MS Access " & _
"97 Database;" & _
"DBQ=g:\#Train\OldAccess" & _
"Program_Keep\SecureMatrix.mdb;" & _
"DefaultDir=g:\#Train\OldAccess" & _
"Program_Keep;Driv"), _
Array("erId=281;FIL=MSAccess;Max" & _
"BufferSize=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 '" & dt1 & "'} And " & _
"Level2bucketblank.Date<={ts '" & DT2 & _
"'})" & 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


--
Regards,
Tom Ogilvy

"CLR" <croberts@tampabay.rr.com> wrote in message
news:Of48QmmjGHA.3848@TK2MSFTNGP04.phx.gbl...
> Thanks Don........but I still can't get there from here........I've tried
> all sorts of combinations like that........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> news:uW5UIbmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > usually you can just stop with " & range("a1") & " continue
> >
> > --
> > Don Guillett
> > SalesAid Software
> > dguillett1@austin.rr.com
> > "CLR" <croberts@tampabay.rr.com> wrote in message
> > news:uEbqtFmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > > 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
> > >
> > >
> > >
> > >
> >
> >
>
>

That did the trick Tom.........THANKS HEAPS!!!!! Like you said, there was a
couple of space probs but nothing serious........actually, I wound up just
copying and pasting the "good stuff" out of your post into my recorded macro
and it started working good. That was only a sample Query I made here at
home in my post because I don't have access to the SQL Server from here,
I'll try it on the real one tomorrow, but I don't expect any problems.

Once again, thanking you most kindly........

Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:#4f#GynjGHA.3848@TK2MSFTNGP04.phx.gbl...
> This may have a space problem or two, so check it closely, but it should
be
> close:
>
> Sub query2()
> Dim DT1 As String, DT2 as String
>
> DT1 = Format(Range("B9").Value, _
> "YYYY-MM-DD 00:00:00")
>
> DT2 = Format(Range("B10").Value, _
> "YYYY-MM-DD 00:00:00")
>
> With ActiveSheet.QueryTables.Add(Connection:= _
> Array(Array("ODBC;DSN=MS Access " & _
> "97 Database;" & _
> "DBQ=g:\#Train\OldAccess" & _
> "Program_Keep\SecureMatrix.mdb;" & _
> "DefaultDir=g:\#Train\OldAccess" & _
> "Program_Keep;Driv"), _
> Array("erId=281;FIL=MSAccess;Max" & _
> "BufferSize=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 '" & dt1 & "'} And " & _
> "Level2bucketblank.Date<={ts '" & DT2 & _
> "'})" & 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
>
>
> --
> Regards,
> Tom Ogilvy
>
> "CLR" <croberts@tampabay.rr.com> wrote in message
> news:Of48QmmjGHA.3848@TK2MSFTNGP04.phx.gbl...
> > Thanks Don........but I still can't get there from here........I've
tried
> > all sorts of combinations like that........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> > news:uW5UIbmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > > usually you can just stop with " & range("a1") & " continue
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > dguillett1@austin.rr.com
> > > "CLR" <croberts@tampabay.rr.com> wrote in message
> > > news:uEbqtFmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Hi Tom...........I'm at work now and got to try your code on the real thing
Query to the SQL Server, and although it took me several tries because of the
way the editor handles those line-break thingies, IT WORKS BEAUTIFULLY!!!!!
Life is good, thanks to you.

Microsoft should give you lots and lots of money!!!........your help to us
makes their Excel program much more usable and understandable.

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

> This may have a space problem or two, so check it closely, but it should be
> close:
>
> Sub query2()
> Dim DT1 As String, DT2 as String
>
> DT1 = Format(Range("B9").Value, _
> "YYYY-MM-DD 00:00:00")
>
> DT2 = Format(Range("B10").Value, _
> "YYYY-MM-DD 00:00:00")
>
> With ActiveSheet.QueryTables.Add(Connection:= _
> Array(Array("ODBC;DSN=MS Access " & _
> "97 Database;" & _
> "DBQ=g:\#Train\OldAccess" & _
> "Program_Keep\SecureMatrix.mdb;" & _
> "DefaultDir=g:\#Train\OldAccess" & _
> "Program_Keep;Driv"), _
> Array("erId=281;FIL=MSAccess;Max" & _
> "BufferSize=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 '" & dt1 & "'} And " & _
> "Level2bucketblank.Date<={ts '" & DT2 & _
> "'})" & 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
>
>
> --
> Regards,
> Tom Ogilvy
>
> "CLR" <croberts@tampabay.rr.com> wrote in message
> news:Of48QmmjGHA.3848@TK2MSFTNGP04.phx.gbl...
> > Thanks Don........but I still can't get there from here........I've tried
> > all sorts of combinations like that........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> > news:uW5UIbmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > > usually you can just stop with " & range("a1") & " continue
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > dguillett1@austin.rr.com
> > > "CLR" <croberts@tampabay.rr.com> wrote in message
> > > news:uEbqtFmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>

Hi Tom...........I'm at work now and got to try your code on the real thing
Query to the SQL Server, and although it took me several tries because of the
way the editor handles those line-break thingies, IT WORKS BEAUTIFULLY!!!!!
Life is good, thanks to you.

Microsoft should give you lots and lots of money!!!........your help to us
makes their Excel program much more usable and understandable.

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

> This may have a space problem or two, so check it closely, but it should be
> close:
>
> Sub query2()
> Dim DT1 As String, DT2 as String
>
> DT1 = Format(Range("B9").Value, _
> "YYYY-MM-DD 00:00:00")
>
> DT2 = Format(Range("B10").Value, _
> "YYYY-MM-DD 00:00:00")
>
> With ActiveSheet.QueryTables.Add(Connection:= _
> Array(Array("ODBC;DSN=MS Access " & _
> "97 Database;" & _
> "DBQ=g:\#Train\OldAccess" & _
> "Program_Keep\SecureMatrix.mdb;" & _
> "DefaultDir=g:\#Train\OldAccess" & _
> "Program_Keep;Driv"), _
> Array("erId=281;FIL=MSAccess;Max" & _
> "BufferSize=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 '" & dt1 & "'} And " & _
> "Level2bucketblank.Date<={ts '" & DT2 & _
> "'})" & 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
>
>
> --
> Regards,
> Tom Ogilvy
>
> "CLR" <croberts@tampabay.rr.com> wrote in message
> news:Of48QmmjGHA.3848@TK2MSFTNGP04.phx.gbl...
> > Thanks Don........but I still can't get there from here........I've tried
> > all sorts of combinations like that........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> > news:uW5UIbmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > > usually you can just stop with " & range("a1") & " continue
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > dguillett1@austin.rr.com
> > > "CLR" <croberts@tampabay.rr.com> wrote in message
> > > news:uEbqtFmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>

Hi Tom...........I'm at work now and got to try your code on the real thing
Query to the SQL Server, and although it took me several tries because of the
way the editor handles those line-break thingies, IT WORKS BEAUTIFULLY!!!!!
Life is good, thanks to you.

Microsoft should give you lots and lots of money!!!........your help to us
makes their Excel program much more usable and understandable.

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

> This may have a space problem or two, so check it closely, but it should be
> close:
>
> Sub query2()
> Dim DT1 As String, DT2 as String
>
> DT1 = Format(Range("B9").Value, _
> "YYYY-MM-DD 00:00:00")
>
> DT2 = Format(Range("B10").Value, _
> "YYYY-MM-DD 00:00:00")
>
> With ActiveSheet.QueryTables.Add(Connection:= _
> Array(Array("ODBC;DSN=MS Access " & _
> "97 Database;" & _
> "DBQ=g:\#Train\OldAccess" & _
> "Program_Keep\SecureMatrix.mdb;" & _
> "DefaultDir=g:\#Train\OldAccess" & _
> "Program_Keep;Driv"), _
> Array("erId=281;FIL=MSAccess;Max" & _
> "BufferSize=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 '" & dt1 & "'} And " & _
> "Level2bucketblank.Date<={ts '" & DT2 & _
> "'})" & 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
>
>
> --
> Regards,
> Tom Ogilvy
>
> "CLR" <croberts@tampabay.rr.com> wrote in message
> news:Of48QmmjGHA.3848@TK2MSFTNGP04.phx.gbl...
> > Thanks Don........but I still can't get there from here........I've tried
> > all sorts of combinations like that........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> > news:uW5UIbmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > > usually you can just stop with " & range("a1") & " continue
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > dguillett1@austin.rr.com
> > > "CLR" <croberts@tampabay.rr.com> wrote in message
> > > news:uEbqtFmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>

Chuck,

Glad it helped.

--
Regards,
Tom Ogilvy

"CLR" <CLR@discussions.microsoft.com> wrote in message
news:75F01C96-9003-4726-86B9-34B101A6E301@microsoft.com...
> Hi Tom...........I'm at work now and got to try your code on the real
thing
> Query to the SQL Server, and although it took me several tries because of
the
> way the editor handles those line-break thingies, IT WORKS
BEAUTIFULLY!!!!!
> Life is good, thanks to you.
>
> Microsoft should give you lots and lots of money!!!........your help to us
> makes their Excel program much more usable and understandable.
>
> Thanks again,
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Tom Ogilvy" wrote:
>
> > This may have a space problem or two, so check it closely, but it should
be
> > close:
> >
> > Sub query2()
> > Dim DT1 As String, DT2 as String
> >
> > DT1 = Format(Range("B9").Value, _
> > "YYYY-MM-DD 00:00:00")
> >
> > DT2 = Format(Range("B10").Value, _
> > "YYYY-MM-DD 00:00:00")
> >
> > With ActiveSheet.QueryTables.Add(Connection:= _
> > Array(Array("ODBC;DSN=MS Access " & _
> > "97 Database;" & _
> > "DBQ=g:\#Train\OldAccess" & _
> > "Program_Keep\SecureMatrix.mdb;" & _
> > "DefaultDir=g:\#Train\OldAccess" & _
> > "Program_Keep;Driv"), _
> > Array("erId=281;FIL=MSAccess;Max" & _
> > "BufferSize=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 '" & dt1 & "'} And " & _
> > "Level2bucketblank.Date<={ts '" & DT2 & _
> > "'})" & 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
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "CLR" <croberts@tampabay.rr.com> wrote in message
> > news:Of48QmmjGHA.3848@TK2MSFTNGP04.phx.gbl...
> > > Thanks Don........but I still can't get there from here........I've
tried
> > > all sorts of combinations like that........
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > > "Don Guillett" <dguillett1@austin.rr.com> wrote in message
> > > news:uW5UIbmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > > > usually you can just stop with " & range("a1") & " continue
> > > >
> > > > --
> > > > Don Guillett
> > > > SalesAid Software
> > > > dguillett1@austin.rr.com
> > > > "CLR" <croberts@tampabay.rr.com> wrote in message
> > > > news:uEbqtFmjGHA.4748@TK2MSFTNGP04.phx.gbl...
> > > > > 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
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >