Solved: EXCEL VBA - drill down feature

[Bon Vivant]

G'day

I'm new to VBA - so please bear with me.

Set up:
I have an EXCEL file where in Column A contains a date
Column B would have the following text: "Click here for RAW data"
Example:
A _____________B
1/1/2011 _______Click here for RAW data
2/1/2011 _______Click here for RAW data
3/1/2011 _______Click here for RAW data

I would like to offer my customer the feature that when they click on cell B1 or B2 or B3
that a macro is engaged where:
- the date in the corresponding A cell is passed to a database query and used as part of the selection criteria
- That a new EXCEL worksheet is opened with the RAW data included.

The data base is a SYBASE - I got the connection syntax figured out
I need figure out:
- How to pass a parameter ( corresponding A cell)
- How to excute query
- How to open new worksheet with query results.

Connection syntax:

sConnect = "Driver={SYBASE ASE ODBC Driver};Srvr=xx.xx.xx.xx;Uid=user;Pwd=password;"
sSQL = "SELECT * From Customer WHERE (Customer.Date =Value from corresponding A cell"

Cheers & Thanx

[Kenneth Hobs]

Welcome to the forum!

String concatenation and Offset is needed.
sSQL = "SELECT * From Customer WHERE (Customer.Date = " & activecell.offset(,-1).Value & ")"

You may need to add single quotes around the value.

[Bon Vivant]

Hi Kenneth,

Wow.. quick response.

I'm new to this . so I do apologies for asking dumb question.

So.. How I would go about and open a new worksheet and dump SQL results into said worksheet.

Thanx again.
Michel

[Kenneth Hobs]

A recorded macro will show how to do parts like Opening a workbook.

I thought that you knew how to do the SQL part? I use ADO for that type of thing. A QueryTable could be used too. Some ADO help: http://www.erlandsendata.no/english/index.php?t=envbadac