SQL query in Excel 2000 using VBA

[Brandon A. Dreiling]

Greetings,

I am looking for help on how to reference a specific worksheet cell to be
used in a SQL (Sybase 11) query.

I have working VBA code that will pull data as long as I hard code the date
or date range; however, I would like a single cell, or even a msgbox to be
used for the date.

If anyone has any ideas, I would appreciate it.

Thanks,
Brandon

 

[Bob Phillips]

SQL query in Excel 2000 using VBABrandon,

Essentially, it seems that Application.Inputbox with a type of 8 would be what you want, but post the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Greetings,

I am looking for help on how to reference a specific worksheet cell to be used in a SQL (Sybase 11) query.

I have working VBA code that will pull data as long as I hard code the date or date range; however, I would like a single cell, or even a msgbox to be used for the date.

If anyone has any ideas, I would appreciate it.

Thanks,
Brandon

 

[Dick Kusleika]

Brandon

See if this helps

http://www.dicks-clicks.com/excel/ExternalData6.htm#Parameters

 

[Brandon A. Dreiling]

Thanks for offering to help. Below is the query. I need to be able to have
the date 2004-03-18 pulled from a cell in a workbook and have the other date
2004-03-19 generated by adding 1 to the date from the cell.

Thanks again,
Brandon


SELECT tbl_ticket.vendor_code, tbl_ticket.reported_on,
tbl_ticket.work_queue_code, tbl_ticket.ticket_status_code,
tbl_ticket.ticket_num FROM BB_TT.dbo.tbl_ticket tbl_ticket WHERE
(tbl_ticket.reported_on>{ts '2004-03-18 00:00:00'} And
tbl_ticket.reported_on<{ts '2004-03-19 00:00:00'}) AND
(tbl_ticket.ticket_status_code<>2) ORDER BY tbl_ticket.vendor_code

 

[Bob Phillips]

Re: SQL query in Excel 2000 using VBAHi Brandon,

Assuming that the date is in cell A1 on Sheet 1, try this

dtTest = Format(Worksheets("Sheet1").Range("A1").Value, "yyyy-mm-dd")
dtTest2 = Format(Worksheets("Sheet1").Range("A1").Value + 1, "yyyy-mm-dd")
sSQL = "SELECT tbl_ticket.vendor_code, " & _
" tbl_ticket.reported_on, " & _
" tbl_ticket.work_queue_code, " & _
" tbl_ticket.ticket_status_code, " & _
" tbl_ticket.ticket_num " & _
"FROM BB_TT.dbo.tbl_ticket tbl_ticket" & _
"WHERE (tbl_ticket.reported_on>{ts '" & dtTest & "'} And " & _
" tbl_ticket.reported_on<{ts '" & dtTest2 & "'}) AND " & _
" (tbl_ticket.ticket_status_code<>2)" & _
"ORDER BY tbl_ticket.vendor_code"

and use sSQL in your query

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Thanks for offering to help. Below is the query. I need to be able to have the date 2004-03-18 pulled from a cell in a workbook and have the other date 2004-03-19 generated by adding 1 to the date from the cell.

Thanks again,
Brandon


SELECT tbl_ticket.vendor_code, tbl_ticket.reported_on, tbl_ticket.work_queue_code, tbl_ticket.ticket_status_code, tbl_ticket.ticket_num FROM BB_TT.dbo.tbl_ticket tbl_ticket WHERE (tbl_ticket.reported_on>{ts '2004-03-18 00:00:00'} And tbl_ticket.reported_on<{ts '2004-03-19 00:00:00'}) AND (tbl_ticket.ticket_status_code<>2) ORDER BY tbl_ticket.vendor_code





Brandon,

Essentially, it seems that Application.Inputbox with a type of 8 would be what you want, but post the code.

 

[Brandon]

Bob,

Thanks again for your help. I tried using the example you posted in my
query to no avail. I am still unable to get the query to use a variable
to read from a cell in a worksheet.

When I add breakpoints to verify individual steps, I can see that the
cell value is being assigned to the variables as needed; however, I am
still not getting the variable into the SQL query properly.

When I remove all variables from the query and replace them with a date
stamp in yyyy-mm-dd hh:mm:ss format, the data is returned as needed.

Here is the complete block of code for this query/process. Could you
please help me once more in getting this to work?

Thanks again,

Brandon

Workbooks("MyBook.xls").Activate
Dim dtTest As Date
Dim dtTest2 As Date
Dim sSQL As String
Worksheets("MySheet").Select
dtest = Workbooks("MyBook.xls").Worksheets("MySheet").Range("A3").Value
dtest2 =
Workbooks("MyBook.xls").Worksheets("MySheet").Range("A3").Value + 1
Windows("MyBook.xls").Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=SERVER;SRVR=SERVER;DB=DB;UID=LOGIN;PWD=PASSWORD", _
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT tbl_common_ticket.vendor_code,
tbl_common_ticket.reported_on" & Chr(13) & "" & Chr(10) & "FROM
DB.dbo.tbl_common_ticket tbl_common_ticket" & Chr(13) & "" & Chr(10) &
"WHERE (tbl_common_ticket.reported_on>={ts '" & dtest & "'} And
tbl_common" _
, _
"_ticket.reported_on<={ts '" & dtest2 & "'})" & Chr(13) & "" &
Chr(10) & "ORDER BY tbl_common_ticket.vendor_code,
tbl_common_ticket.reported_on" _
)
.Name = "Reported"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

 

[NickHK]

Brandon,
What are yopu hoping this expression

={ts '" & dtest & "'}

will evalutae to ?

NickHK

 

[Brandon]

Thanks for your reply.

The two variables will create a date/time range.

={ts '" & dtest & "'} is to state greater or equal to a date and

timestamp, such as 2004-04-07 00:00:00 and the next variable will state
less than or equal to 2004-04-08 00:00:00.

Basically it is scaling down to a 24 hour time frame that will change
each day.

Thanks for your help,
Brandon

 

[NickHK]

Brandon,
I'm no DB expert, but a couple of thing would simplify your task.
There no point formatting the SQL statement with "& Chr(13) & "" & Chr(10)"
as this is irrelevant to ADO, unless you want it to look nice in the debug
window.
Not sure how ADO would handle the "{" barcket in the SQL, unless they are
required by your DB.

Depending on how the data is held in "Range("A3").Value" you may be
returning an unexpected value to the dtest variable.
Add a Debug.Print dtest and see what is being passed.

To me it seems that the part "{ts '" & dtest & "'}" would evalaute to
something like
{ts '08-Apr-04 8:00:43 PM'}
which is probably not what you are storing in the DB, as the ts variable is
not being evaluated as it is in the string.

If you wish to use the "(" brakets for clarity, it would be better to
contain to two caparions either side of the AND separately.

Hope that helps.

NickHK