Hi,

I am trying to run SQL queries directly from a worksheet in Excel. I have
found an add-in for Excel 2002 that is no longer supported in 2003 or 2007
which is just what I want (xlodbc.xla).

1. Why was this discontinued?
2. Is there a built-in way to do this in newer versions [running queries
from a cell, NOT going to Data > Import > etc...]?
3 Any other thoughts, help, advice?

Thanks!

If you are looking to run a parameter query there are much better ways, You
may find this helpful.

http://www.nickhodge.co.uk/gui/datam...taexamples.htm

About 3/4 way down

For all it's failings in 2007, data connections is not one of them.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.nickhodge.co.uk




"adamscottmartin" <adamscottmartin[@NOSPAM]hotmail[dot.com]> wrote in
message news:9420DD40-6F95-4C34-B9D5-D6087A83CAF5@microsoft.com...
> Hi,
>
> I am trying to run SQL queries directly from a worksheet in Excel. I have
> found an add-in for Excel 2002 that is no longer supported in 2003 or 2007
> which is just what I want (xlodbc.xla).
>
> 1. Why was this discontinued?
> 2. Is there a built-in way to do this in newer versions [running queries
> from a cell, NOT going to Data > Import > etc...]?
> 3 Any other thoughts, help, advice?
>
> Thanks!

"Nick Hodge" <nick_ho...@zen.co.uk> wrote...
>If you are looking to run a parameter query there are much better
>ways, You may find this helpful.
>
>http://www.nickhodge.co.uk/gui/datam...mples/external...
>
>About 3/4 way down
>
>For all it's failings in 2007, data connections is not one of them.
....

It's not quite what the OP is asking for. The approach your webpage
describes is fine if all one needs is a single parametrized query
running against a STATIC data source. SQL.REQUEST, OTOH, can use
arguments evaluating to text strings representing queries too complex
for Microsoft Query, *AND* SQL.REQUEST could use expressions as the
1st argument (connection string) so one SQL.REQUEST formula could
query different data sources depending on whether one were connected
to a network or standalone.

Maybe data connections are useful, but they DON'T provide anywhere
near the functionality of SQL.REQUEST.

As for why it's no longer included on product CDs, Microsoft seems to
have decided that real developers and remaining power users would
write UDFs for their dynamic queries. For everyone else, let 'em use
menu commands and dialogs. While there may be insufficient ROI to
rewrite XLODBC.XLA in VBA or as an XLL, there's no obvious reason
Microsoft couldn't still provide it on production CDs. This supports a
hypothesis that I have that Microsoft really doesn't give a rat's
backside for its customers as long as they keep buying upgrades.

Thanks for the help to both of you.

I have already tried and failed to solve my task using the data sources.
This is why I went looking for something like xlodbc.xla.

I have two major tasks that I would like to accomplish which an inline
query/SQL command would work beautifully:

1. I need to get data from a column in a table from one of my data sources
and truncate all of the results to a certain number of characters. A cherry
on top would be to also check these results for duplicates.
2. I have a column in a table of one of my data sources that has either a Y
or N in each cell. I need to convert all the Y's to M and all the N's to P.

One question that someone may be able to answer reasonably easily: when I am
using the Microsoft Query Editor built in to Excel, am I using the SQL syntax
of MS SQL or the syntax of the data source host (in this case Centura SQL)?

Also I am not super familiar with MS SQL but I am with MySQL and more
vaguely with a couple of others. Is there a good primer out there on the web
that can get me through some of the basic commands? I have already Google'd
and found nothing that was significantly helpful.

Thanks again!

"Harlan Grove" wrote:

> "Nick Hodge" <nick_ho...@zen.co.uk> wrote...
> >If you are looking to run a parameter query there are much better
> >ways, You may find this helpful.
> >
> >http://www.nickhodge.co.uk/gui/datam...mples/external...
> >
> >About 3/4 way down
> >
> >For all it's failings in 2007, data connections is not one of them.
> ....
>
> It's not quite what the OP is asking for. The approach your webpage
> describes is fine if all one needs is a single parametrized query
> running against a STATIC data source. SQL.REQUEST, OTOH, can use
> arguments evaluating to text strings representing queries too complex
> for Microsoft Query, *AND* SQL.REQUEST could use expressions as the
> 1st argument (connection string) so one SQL.REQUEST formula could
> query different data sources depending on whether one were connected
> to a network or standalone.
>
> Maybe data connections are useful, but they DON'T provide anywhere
> near the functionality of SQL.REQUEST.
>
> As for why it's no longer included on product CDs, Microsoft seems to
> have decided that real developers and remaining power users would
> write UDFs for their dynamic queries. For everyone else, let 'em use
> menu commands and dialogs. While there may be insufficient ROI to
> rewrite XLODBC.XLA in VBA or as an XLL, there's no obvious reason
> Microsoft couldn't still provide it on production CDs. This supports a
> hypothesis that I have that Microsoft really doesn't give a rat's
> backside for its customers as long as they keep buying upgrades.
>