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

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)

"Brandon A. Dreiling" wrote in message ...
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

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



On 4/2/04 6:34 AM, in article , "Bob
Phillips" wrote:

Brandon,

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

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)

"Brandon A. Dreiling" wrote in message ...
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



On 4/2/04 6:34 AM, in article , "Bob Phillips" wrote:


Brandon,

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

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").Rang e("A3").Value
dtest2 =
Workbooks("MyBook.xls").Worksheets("MySheet").Rang e("A3").Value + 1
Windows("MyBook.xls").Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=SERVER;SRVR=SERVER;DB=DB;UID=LOGIN;PWD=P ASSWORD", _
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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Brandon,
What are yopu hoping this expression
={ts '" & dtest & "'}
will evalutae to ?

NickHK

"Brandon" <bdreilingATmac.com wrote in message
...


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").Rang e("A3").Value
dtest2 =
Workbooks("MyBook.xls").Worksheets("MySheet").Rang e("A3").Value + 1
Windows("MyBook.xls").Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=SERVER;SRVR=SERVER;DB=DB;UID=LOGIN;PWD=P ASSWORD", _
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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Brandon

See if this helps

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

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Brandon A. Dreiling" wrote in message
...
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