You can certainly attach to an Excel spreadsheet and use it as a database, with
SQL.

However, it sounds as though you're talking about using SQL on a workbook  from
within the same workbook. Although I've never tried it, I would imagine that it
would be most unlikely to work.

At best, I'd expect the SQL statements to refer to the copy of the workbook that
is on disk, not the one you have open. At worst, I'd expect file access
conflict or straight Excel crashes.

By all means give it a go. A quick Google search will lead you to the connect
strings needed to open an Excel sheet  as an ODBC database.

I going to disagree with Dave (sorry Dave).. but I don't
think you can. My guess would be that you would need
Access as an "intermediary" -- that you'd need to extract
the data  from Excel and populate an MDB and recordset, then
apply SQL, and then dump the results of your query back
to Excel. I hope I'm wrong.

This can be done in fact I am also currently looking nto a similiar solution.
You can use the adodb object and open recordset by using TXT driver.On using
TXT driver you can select data  from file as if it was a table. Once you have the
data in record set you can put  it in EXCEL file ,I have got the code  from
internet but can put it up on 9 Jan.

I am a newbie (a real newb), and I can write only simplest codes in VB.
I have a problem and it is for sure too simple for most of you.
I have an excel  workbook which has several sheets and the data  on them are
updated daily. I want to make it a bit smart so that upon inserting certain
values in a column the specific rows are cut from that sheet  and are pasted on
another desired sheet in the last row. I have experimented with the limited VB
vocabulary that I have without success. Plz guide me at ur convenience.....

You should NOT use another thread to post a new question. Please start a new
thread and repost your question with an appropriate subject so people can
recognize your question and not assume it is another answer in the same
thread!

> This can be done in fact I am also currently looking nto a similiar
solution.
You can use the adodb object and open recordset by using TXT driver.On
using TXT driver you can select data  from file as if it was a table. Once
you have the data in record set you can put  it in EXCEL file ,I have got the
code from internet but can put it up on 9 Jan.<

Yup...I do it all the time only I prefer to use Word, but Excel works
too...just a little differently but the base is the same.

I make a living using ADO to access DBs
through Word (See http://www.mousetrax.com/Consulting_Solutions.html)

Granted, yes most of my work is using Word to access data  via ADO in a DB,
but there is also a PROVIDER (DATA term for an ADO Driver...for Excel).

So it depends on FROM WHERE he wants to access the data. But Excel CAN ACT
as a flat DB...so it can be done.

you can get the Excel ADO Provider here:
http://www.connectionstrings.com/

have you ever access an SQL DB via an ASP page? Almost the same thing
if you have. I have a sample of how to access an Access DB from Word using
ADO here: http://www.mousetrax.com/techpage.html#autoforms see # 5 in the
Please Fill out this Form series. You can download the sample with the form
and DB. I know this isn't exactly what you wanted, but you can see in the
code how SQL is used to query the DB from Word to get the RecordSet. Them
VBA is used to assemble the info in Word. Similar to using VBS and HTML in
and ASP page.

Now granted...I haven't checked out this full thread, so I'm not entirely
sure this is what you're asking to do. But you CAN "pretend" Excel is a
flat-file DB and use the Excel Provider at the above URL to connect.

Provide more details if that's not what you were asking.

This Microsoft article might be of assistance.

How To Use ADO with Excel Data from Visual Basic or VBA
support.microsoft.com/default.aspx

I don't think I explained myself very well anyway.

There are OLE connection strings that can be used to access an Excel spreadsheet
as though it is a database. See, for instance,
http://www.connectionstrings.com/, and scroll down to the Excel ones.

You can access ODBC connections from Excel using connection strings.

So I'd expect that one Excel workbook  could cheerfully access another Excel
workbook using an ODBC connection and SQL statements.

But ... I doubt that a workbook could access ITSELF using SQL statements. It
might possibly be able to access the disk copy of itself, although I suspect
that even this would give file access contention for SQL statements that update
the file.

I've never tried to link Excel to Excel in this way, nor do I anticipate wanting
to do so. In the past, I've certainly linked to Excel from Access (although I
simply mapped the spreadsheet as a table, rather than using an ODBC connection
string and VBA code).

I am currently updating a system that accesses Sybase databases from Excel using
appropriate connection strings. For testing at home, I swap the connection
string to an Access one, and thus have Excel using SQL to manipulate an Access
database.

>I am currently updating a system that accesses Sybase databases from Excel
using appropriate connection strings. For testing at home, I swap the
connection string to an Access one, and thus have Excel using SQL to
manipulate an Access database.

HA...I did nearly the same thing via Word <-> Sybase and then Access. I used
SQL Anywhere as a wrapper to access the Sybase DB. Never did that
before...never will again!<g>

It was a hassle to learn and thanks to Greg who put  in several hours helping
me figure it out and doing server tests. I actually did get it working well,
but the client had problems accessing the DB and getting the code  to work.
So he decided to use a daily data  merge of the one table from Sybase into
Access...which was also the way we were testing the process during
dev...prior to final implementation.

Again, all worked well on my end, but his was giving him headaches.

After weeks of hassle and troubleshooting between us...we discovered he had
Acrobat 7 installed and THAT @#$%@! addin was interfering with everything
and messing things up on his end!<grrrrr, grumble, curse> Once he removed
the Acrobat 7 Word Addin, everything worked great on his end. And, although
we never went back to test, I have a strong feeling that was the problem
with the Sybase/SQL Anywhere connection.

But that whole experience convinced me to no longer bother trying to work
with Office apps and anything outside of MS DB apps (Access, SQL, Excel).

Perhaps the weirdest part of the system I'm working on at the moment ...

Excel VBA uses some data  out of its own worksheet,
Then retrieves data from a Sybase database,
Then opens and significantly reformats a Word document "template" to produce a
customer letter.

I inherited the requirement for Excel to be the main controller - I would never
have designed it that way. However, it all works well and reasonably quickly.

>I inherited the requirement for Excel to be the main controller - I would
never have designed it that way. However, it all works well and reasonably
quickly.

Cool. Yeah, being a Wordie at heart, I always use Word to control traffic,
but as I said in the previous email, my Sybase experience causes me to run
screaming from non-MS DBs when working with Word...even if the real problem
was the client and his troubleshooting skills.

Telecommuting/Consulting is fun. But there ARE times when I'd just like to
reach through the phone/email, grab the client and scream...WOULD YOU PLEASE
READ THE DIRECTIONS I SENT YOU AND DO IT AS I TOLD YOU TO DO IT!!!!!! (I'd
mentioned the addin issue much earlier in the project, but he blew it
off...sure the problem wasn't HIS

But there ARE times when I'd just like to reach through the phone/email,
grab the client and scream...WOULD YOU PLEASE READ THE DIRECTIONS I SENT
YOU AND DO IT AS I TOLD YOU TO DO IT!!!!!!

Yeah, most all my clients I enjoy. But there are those few
who...when I see their email in my box...I just wanna run away or scream!