Reading SQLServer Data Base into Excel

[MichaelW.]

I need to be able to push a button on an Excel Worksheet
and execute VB code that will retrieve data from a
SQLServer data base into an Excel Worksheet. The data
is "hourly" records and I will need to query and retrieve
the data for the current day. I have good programming
knowledge of Access and know how I would do this in Acess
but my Excel skills are limited.

I have sucessfully added a button to the worksheet that
executes VB code that connects to the SQL Server data
base using (ODBC data source) using the OpenConnection
method but I am not sure where to go from here.

Does anyone have a simple start to finish example of
connecting to a SQLServer data base and retrieiving into
Excel?

Thanks.


Oh, by the way I started out doing this using MSQuery and
successfully retrieved the data back into Excel for
a "hardcoded" date. However, I was not sure how to pass
MSQuery a paramter to retrieve the "current" date. From
this I decided I would rather use VB code instead of
MSQuery but maybe this is not the best approach.

Any advice? Maybe an even better way?

Thanks, again.

 

[Jan Karel Pieterse]

Hi Michael,

I was not sure how to pass
MSQuery a paramter to retrieve the "current" date. From
this I decided I would rather use VB code instead of
MSQuery but maybe this is not the best approach.

Any advice? Maybe an even better way?

Dick Kusleika and I are (slowly) creating a query manager with which
one can easily add parameters to existing queries (which is what you
need to do I guess) and with which you can easily edit the sql of your
query. Send me a message if you'd like a (beta) copy to try.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Jan Karel Pieterse]

Hi Jan,

Send me a message if you'd like a (beta) copy to try.

Even better:

download at www.jkp-ads.com/QueryManager.zip

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Dick Kusleika]

Michael:

You don't need to use VBA. Read this

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

Jan:

Oh, I made some good headway this week. I can see the light at the end of
the tunnel.<g>

 

[Jan Karel Pieterse]

Hi Dick,

I can see the light at the end of
the tunnel.<g>

Hope it is not a train <bg>

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com