Hello,

I am an experienced Excel user just starting to build VB
code/scripts. I'm trying to put a SQL query (from an ODBC
data source) into a macro, so that when it runs, it
returns the result set to a specified range in a worksheet
(i.e. Sheet2!A1).

The query is something like:

Select serialnum, count(serialnum)
from TicketGrids
where serialnum like sheet1!a2

and other queries are very complex involving inner joins,
full outer joins and views from the SQL database.

I am trying to automate a complex report where data is
currently manually imported.

Thanks in advance!

Dan

I assume you mean excel VBA not Visual Basic...
I assume you dont want a parameter query, but just need some syntax
explanation on string manipulation:

either

dim sqlString as string

sqlstring = "Select * from mytable where field like " & _
Range("sheet1!a2").value

or..

i often use following approach when i have a series of similar jobs to
do ..it's not faster but it can make the code easier to read and edit.

'assign the basic query to a constant
const sqlFIX = "select * from |TBL| where |FLD| like |VAL|"
dim sqlstr as string

'replace the 'variables' with variable data
sqlstr = sqlfix
sqlstr = replace(sqlstr,"|TBL|",[sheet1!a1])
sqlstr = replace(sqlstr,"|FLD|",[sheet1!a2])
sqlstr = replace(sqlstr,"|VAL|",[sheet1!a3])


note that [sheet1!a1] is short syntax for
Evaluate("sheet1!a1") which resolves to Range("sheet1!a1")

keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dan" wrote:

Hello,

I am an experienced Excel user just starting to build VB
code/scripts. I'm trying to put a SQL query (from an ODBC
data source) into a macro, so that when it runs, it
returns the result set to a specified range in a worksheet
(i.e. Sheet2!A1).

The query is something like:

Select serialnum, count(serialnum)
from TicketGrids
where serialnum like sheet1!a2

and other queries are very complex involving inner joins,
full outer joins and views from the SQL database.

I am trying to automate a complex report where data is
currently manually imported.

Thanks in advance!

Dan

Very helpful. Thanks! --Dan

keepITcool wrote ...

i often use following approach when i have a series of similar jobs to
do ..it's not faster but it can make the code easier to read and edit.

'assign the basic query to a constant
const sqlFIX = "select * from |TBL| where |FLD| like |VAL|"
dim sqlstr as string

'replace the 'variables' with variable data
sqlstr = sqlfix
sqlstr = replace(sqlstr,"|TBL|",[sheet1!a1])
sqlstr = replace(sqlstr,"|FLD|",[sheet1!a2])
sqlstr = replace(sqlstr,"|VAL|",[sheet1!a3])

That's a good tip <g.

Jamie.

--

(Jamie Collins) wrote:

keepITcool wrote ...

{snip]

That's a good tip <g.

Jamie.

--


finally you agree with something i say.. <g

btw: ado other thread you referred to
BUG OpenSchema(adSchemaTables)

discrepancy ODBC/ADO Table and System Tables.
I just checked with ado2.8 and xl2003, latest jetSP....
still the same :(


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool

keepITcool wrote ...

finally you agree with something i say.. <g

That's me: harsh but fair <vbg.

Jamie.

--