H
Hande & Tolga
Below is the original code we use in our VB to retrieve
data directly from database. Instead of using a fixed
activation date "2003-09-17 00:00:00" (please check the
location in the code below) we want to replace that with
a variable where it makes the code more flexible such as
Temp1 where we enter or define the date to Temp1 earlier
in the code. We would appreciate if someone could help
us with the syntax.
Actual Code
-----------
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL
Server;SERVER=AAA_BBBBBBB;UID=TTTTTTT;PWD=TTTTTTT;APP=Micr
osoft Office XP;WSID=TTTTTTT;DATABASE=reports" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Controlling_stop_invoice.Contract,
Controlling_stop_invoice.A,
Controlling_stop_invoice.Currency,
Controlling_stop_invoice.Payment_Due_Date,
Controlling_stop_invoice.Debt_ID, Controlling_stop_i" _
, _
"nvoice.Principal,
Controlling_stop_invoice.Activation_Date,
Controlling_stop_invoice.Units,
Controlling_stop_invoice.Vehicle_Group" & Chr(13) & "" &
Chr(10) & "FROM reports.dbo.Controlling_stop_invoice
Controlling_stop_invoice" & Chr(13) & "" & Chr(10) & "" _
, _
"WHERE (Controlling_stop_invoice.Debt_ID='21')
AND (Controlling_stop_invoice.Activation_Date<={ts '2003-
09-17 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY
Controlling_stop_invoice.Contract" _
)
.Name = "Query from reports_ok_20"
.FieldNames = True
.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
data directly from database. Instead of using a fixed
activation date "2003-09-17 00:00:00" (please check the
location in the code below) we want to replace that with
a variable where it makes the code more flexible such as
Temp1 where we enter or define the date to Temp1 earlier
in the code. We would appreciate if someone could help
us with the syntax.
Actual Code
-----------
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL
Server;SERVER=AAA_BBBBBBB;UID=TTTTTTT;PWD=TTTTTTT;APP=Micr
osoft Office XP;WSID=TTTTTTT;DATABASE=reports" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Controlling_stop_invoice.Contract,
Controlling_stop_invoice.A,
Controlling_stop_invoice.Currency,
Controlling_stop_invoice.Payment_Due_Date,
Controlling_stop_invoice.Debt_ID, Controlling_stop_i" _
, _
"nvoice.Principal,
Controlling_stop_invoice.Activation_Date,
Controlling_stop_invoice.Units,
Controlling_stop_invoice.Vehicle_Group" & Chr(13) & "" &
Chr(10) & "FROM reports.dbo.Controlling_stop_invoice
Controlling_stop_invoice" & Chr(13) & "" & Chr(10) & "" _
, _
"WHERE (Controlling_stop_invoice.Debt_ID='21')
AND (Controlling_stop_invoice.Activation_Date<={ts '2003-
09-17 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY
Controlling_stop_invoice.Contract" _
)
.Name = "Query from reports_ok_20"
.FieldNames = True
.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