Data Import from External Database
 

Not being a VBA whizz i recorded a macro which imports data from an external
database and when i run this macro it works great

One of the fields on this database is a date field and i want this query to
only bring in the records based on a date the user would enter in an input box

I've tried using input box and a variable which would hold the date and
refer to this variable in the line
"WHERE (GL_EXTRACT.RECEIVED_DATE={ts '2009-06-10 00:00:00'})" _
)

the problem i'm having is the date is in double quotes which would make the
variable no good.

is it possible to change this code so that the user can enter a date and the
query would bring back records based on that date?

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 24/06/2009 by User
'

'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DSN=BETN;UID=MRBLOGGS;;DBQ=BETN;DBA=W;APA=T; EXC=F;FEN=T;QTO=F;FRC=10;FDL=10;LOB=T;RST=T;BTD=F; BAM=IfAllSuccessful;NUM=NLS;DPM=F;" _
), Array("MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;")) ,
Destination:=Range("A1" _
))
.CommandText = Array( _
"SELECT GL_EXTRACT.EXTRACTN, GL_EXTRACT.RECEIVED_DATE,
GL_EXTRACT.RECORD_COUNT, GL_EXTRACT.ACCOUNT_TYPE, GL_EXTRACT.ACCOUNTN,
GL_EXTRACT.BUS_CLASS, GL_EXTRACT.FUND_CLASS, GL_EXTRACT.CO_CODE, GL_EXTRACT" _
, _
".ORACLE_AC, GL_EXTRACT.INTERFUND, GL_EXTRACT.DAYS_CREDITS,
GL_EXTRACT.DAYS_DEBITS" & Chr(13) & "" & Chr(10) & "FROM UNIWIN.GL_EXTRACT
GL_EXTRACT" & Chr(13) & "" & Chr(10) & "WHERE (GL_EXTRACT.RECEIVED_DATE={ts
'2009-06-10 00:00:00'})" _
)
.Name = "Query from BETN"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

Data Import from External Database
 

thedate = "2009-06-10"


"WHERE (GL_EXTRACT.RECEIVED_DATE={ts '" & thedate & "'})" )



"Mark" wrote in message
...
Not being a VBA whizz i recorded a macro which imports data from an
external
database and when i run this macro it works great

One of the fields on this database is a date field and i want this query
to
only bring in the records based on a date the user would enter in an input
box

I've tried using input box and a variable which would hold the date and
refer to this variable in the line
"WHERE (GL_EXTRACT.RECEIVED_DATE={ts '2009-06-10 00:00:00'})" _
)

the problem i'm having is the date is in double quotes which would make
the
variable no good.

is it possible to change this code so that the user can enter a date and
the
query would bring back records based on that date?

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 24/06/2009 by User
'

'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DSN=BETN;UID=MRBLOGGS;;DBQ=BETN;DBA=W;APA=T; EXC=F;FEN=T;QTO=F;FRC=10;FDL=10;LOB=T;RST=T;BTD=F; BAM=IfAllSuccessful;NUM=NLS;DPM=F;"
_
), Array("MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;")) ,
Destination:=Range("A1" _
))
.CommandText = Array( _
"SELECT GL_EXTRACT.EXTRACTN, GL_EXTRACT.RECEIVED_DATE,
GL_EXTRACT.RECORD_COUNT, GL_EXTRACT.ACCOUNT_TYPE, GL_EXTRACT.ACCOUNTN,
GL_EXTRACT.BUS_CLASS, GL_EXTRACT.FUND_CLASS, GL_EXTRACT.CO_CODE,
GL_EXTRACT" _
, _
".ORACLE_AC, GL_EXTRACT.INTERFUND, GL_EXTRACT.DAYS_CREDITS,
GL_EXTRACT.DAYS_DEBITS" & Chr(13) & "" & Chr(10) & "FROM UNIWIN.GL_EXTRACT
GL_EXTRACT" & Chr(13) & "" & Chr(10) & "WHERE
(GL_EXTRACT.RECEIVED_DATE={ts
'2009-06-10 00:00:00'})" _
)
.Name = "Query from BETN"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub