How to use SQL on Excel List

[Dingo]

Hi There All!

Am working with a simple (but quite large) Excel list (database) and am
needing to perform quite complex queries on the data.
Have tried to use the 'AdvancedFilter' method but am running into probs.
Ideally I'd like to copy/get the data into a ADO recordset so as to be able
to use SQL queries for all my filtering/appending/...etc.

The Excel list (database) is dynamic and changes often and so can not use
'Get External Data' as this would require the file to be saved each time the
data changes.

So in essence I have a range (e.g. A1:G10000) and I want to use SQL on that
data!

Any suggestions would be very much appreciated.

[Harald Staff]

Hi

See mr Erlandsens page
http://www.erlandsendata.no/english/vba/adodao/index.htm on this.

Best wishes Harald
Excel MVP

Dingo <din...@skybiz.com> skrev i news:3a643...@news1.mweb.co.za...

[Trevor Mills]

I would suggest that if you believe that Excel is slowing down too much then
migrate your data to one of the other database platforms ie Access (DAO, ADO or
ADP) or if your database will grow really big then SQL.

Your data can be moved to any of these platforms with relative ease. I infer
from your question that you do not have much experience with these products and
the questions you need to answer is how much time you have to learn, how critical
your needs are, how important/valuable the data is.

If you start with Access you can build a simple database in a surprisingly short
time and build from there.

hope this helps

Trevor

.

[GraphMan]

Yes indeed! SQL is simpler and superior to using AdvancedFilter.

My suggestion is to use the "Link Table" wizard in access to create a
linked tables to Excel. Your data resides on an Excel worksheet
but "appears" as a linked table in an .mdb file. Now you can SQL to
your hearts content. From inside Excel/VBA you can:

Set recordset_object = OpenRecordset(SQLstatement)

You can blast the results back to another Excel worksheet with
range_object.CopyFromRecordset recordset_object

You can even write back to the original Excel table with SQL "INSERT
INTO" or "UPDATE".

Further - setting up the linked Excel tables using the Access "link
tables" wizard is just a convenience. You can do everything
programmatically from inside VBA using the ADO (or DAO) libraries
rendering Excels AdvancedFilter method obsolete (which it is!).

-Graphman

Sent via Deja.com
http://www.deja.com/

[Dingo]

Thanks Harald - but all the examples presented require either the data to be
stored in an access database or a closed excel workbook, which won't do
unfortunately (the users may not have access and using excel requires that
you save the file each time the data is changed). The data I'm working with
is a list in the workbook I'm writing the code in. This data also changes
allot and so will need to read it into a recordset frequently - i.e. each
time a user makes a selection from a list box.

Is there now way to copy a range of cells into a recordset??

Any further would be really appreciated!

"Harald Staff" <harald...@nrk.no> wrote in message
news:e9$YmE8fAHA.1888@tkmsftngp04...

[Dingo]

Thanks GraphMan - but all the examples presented require either the data to

be stored in an access database or a closed excel workbook, which won't do
unfortunately (the users may not have access and using excel requires that
you save the file each time the data is changed). The data I'm working with
is a list in the workbook I'm writing the code in. This data also changes
allot and so will need to read it into a recordset frequently - i.e. each
time a user makes a selection from a list box.

Is there now way to copy a range of cells into a recordset??

Any further would be really appreciated!

"GraphMan" <tbar...@hotmail.com> wrote in message
news:941sbl$rij$1...@nnrp1.deja.com...

[Harald Staff]

This is leaning towards plilosophy... Can stuff be data unless it's stored
?

Best wishes Harald

Dingo <din...@skybiz.com> wrote in message
news:3a648...@news1.mweb.co.za...

[Rob Rutherford]

XL97. You could try using a query table. The following skeleton code
illustrates the approach. It sets up a query table, and you can define the
sql string as you please. If you are reading data from the workbook
containing the code, then you can dynamically create names pointing to the
tables you want to work with or to cope with the size of the table changing
due to rows being added/deleted. It doesn't seem to mind if you keep
overwriting the table each time you refresh the data or change the query by
changing the sql or the workbook being read.

Private Sub CreateQueryTable()

Dim dbqPath As String
Dim qtb As QueryTable
Const qtbName As String = "MyQueryTable"
Const strCnn As String = "ODBC;Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;"
Dim strSQL As String
Dim wks As Worksheet

'Set up sql. Table1 is a book-level name of
'a table in the workbook with path dbqPath.
strSQL = "SELECT * FROM Table1"
strSQL = strSQL & " ORDER BY x"

'Create the query table on sheet wks with A1 top left.
'dbqPath points to the book containing the source data -
'this can be any open or closed workbook, including this one.
dbqPath = ThisWorkbook.FullName
Set wks = ThisWorkbook.Worksheets("Sheet1")
With wks
.UsedRange.Cells.ClearContents
With .QueryTables.Add(Connection:=strCnn & "DBQ=" & dbqPath & ";",
Destination:=.Range("A1"), Sql:=strSQL)
.Name = qtbName
.FieldNames = True
.Refresh
End With
End With

End Sub

Regards,
Rob Rutherford.

Dingo <din...@skybiz.com> wrote in message

news:3a643...@news1.mweb.co.za...

[GraphMan]

> Thanks GraphMan - but all the examples presented require
> either the data to be stored in an access database or a closed
> excel workbook, which won't do unfortunately (the users may not

> have Access

The user - your Excel application, will have no need at all for
Access. You simply need to have the .mdb file with linked tables to
query against. No data is copied to Access - data only exists on your
spreadsheet. You don't even need Access to create the .mdb file and
linked tables since you can do this entirely with the DAO or ADO
libraries as I do. You might like the Access linked table wizard to
create the file, however, since it is so easy and it leads you by the
nose.

> and using excel
>requires that you save the file each time the data is changed).

Certainly not.

>The data I'm working with
> is a list in the workbook I'm writing the code in. This data also
> changes allot and so will need to read it into a recordset
> frequently - i.e. each
> time a user makes a selection from a list box.

I do this all the time using this technique.

> Is there now way to copy a range of cells into a recordset??

Either you create an .mdb file with a linked table so that you can
set recordset_object = OpenRecordset(SQL_statement)

OR

you create a new recordset object, construct the fields, loop
through each column, row and recordset_object.add.

-Graphman

[GraphMan]

In article <#5xGGaAgAHA.1784@tkmsftngp05>,

"Rob Rutherford" <rob.rut...@bigfoot.com> wrote:
> XL97. You could try using a query table. The following skeleton code
> illustrates the approach.

...More

Whoa! I would love to see this work. Can you clarify?

I'm trying your code with Excel 2000 and am choking at the .Refresh
with the infamous VB error 400 - in other words, crashing without a
clue.

Where on earth do you find the connection string documented. e.g. the
parameter "DriverId=790". Could this be different in Excel 2000?

I do have the ODBC driver "Microsoft Excel Driver(*.xls)" installed but
what is with curly braces {} you put around this string. I can leave
them in or out but neither works. Again - can you point to some
documentation?

The table name you use "FROM Table1" - would that be the .Name property
of the QueryTable object, the worksheet range name of the table, a
worksheet (tab) name, or the VBA logical worksheet object name?

I had no idea that Excel itself had knowledge of SQL. Indeed, if I
examine the "QueryTable" object it does NOT list a .SQL property
although I can now verify that it exists thanks to your post. Where oh
where is there decent documentation for the "QueryTable" object?

I thought that only the DAO or ADO libraries could parse SQL
statements. If there is something built into Excel I would sure like to
see it work!

Thanks in advance -Graphman

[Rob Rutherford]

Table1 in the sql refers to a table set out in Excel like an Access table
e.g.

Num Str
1 a
2 b

Table1 is the workbook-level name you have given to the table (e.g. by using
Insert > Name > Define) and must include the field names at the top of each
column. Also, regard it as a 'proper' database table and don't mix datatypes
within fields 'cos XL won't stop you and the sql then won't be able to
handle it properly.

To get the connection string and clues on things like the sql property, you
could try doing in XL 2000 what I did in XL97:

- Open a new workbook.
- Set up a table like the one shown above (on Sheet1, say) and give it a
name (Table1, say).
- Save the workbook (as c:book1.xls, say).
- Switch on the macro recorder.
- Select Data > Get External Data > Create New Query and the Choose Data
Source dialog box appears.
- Make sure Use the Query Wizard ... is ticked.
- Select the Databases tab and <New Data Source> in the selection box.
- Click OK and the Create New Data Source dialog appears.
- In the 1. What name .... box, type a name (Test, say).
- In the drop-down box for 2. Select a driver ..., choose the Microsoft
Excel Driver.
- Press the 3. Connect button and the ODBC Microsoft Excel Setup dialog
appears.
- Press the Select Workbook button, select c:book1.xls, press OK and then OK
again to get back to the Create New Data Source dialog.
- In the drop-down box for 4. Select a default ...., select Table1 and click
OK.
- You are now back to the Choose Data Source dialog. Press OK and click the
button showing a > sign to choose both columns in Table1. Click the Next
button 3 times and you should have reached Query Wizard - Finish.
- Make sure Return Data to Microsoft Excel is selected and click Finish. The
Returning External Data to Microsoft Excel dialog should appear. Tell it
where to put the query table (Sheet2!A1, say).
- Click OK and you should see your query table appear where you said it
should go. This query table will be called something like ExternalData1 -
you can see this name in the drop-down box of names (note that it is a
sheet-level name).
- Switch off the macro recorder.

Phew! If you've got this far, have a look at the code. In the
QueryTables.Add statement you should see the required connection string
after Driver=. As usual, you get a load of guff, most of which I don't
understand and never use. Follow a similar procedure to get at an Access
table and you should see the connection string for Access. I guess the same
would apply to anything else MSQuery can get at.

XL2000 might be a little different, but I hope this helps.

Regards,
Rob Rutherford

GraphMan <tbar...@hotmail.com> wrote in message

news:944tmf$ff8$1...@nnrp1.deja.com...

[GraphMan]

Whew! I didn't mean to make you work so hard.

Very clever! Use the Excel interface to create the QueryTable object
then examine it.

My problem was caused by not having MS Query installed. Apparently,
the QueryTable object uses this separate application to parse SQL and
retrieve the results. I think Excel is using the old style DDE links
to communicate with this separate application.

In any case, your post has clarified the situation for me and I thank
you - GraphMan