how do I use SQL within vba for excel?

[Guest]

I just want to be able to include SQL statements into a vba environment.
I'm using Office 2003.
Thanks everybody!

 

[Guest]

Here's a simple sample sub:

Sub xx()
Cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\MyExcel.xls;Extended Properties=Excel 8.0;"
Sql = "SELECT * FROM [Sheet1$]"
Set ADORS = CreateObject("ADODB.Recordset")
ADORS.Open Sql, Cnn
End Sub

Create & save a workbook as c:\myexcel.xls (you should populate some columns
in Sheet1) and then run the above sub.

Some hints:

1. You need to know the options for what follows the FROM clause in the
SELECT statement: refer to Excel 2000 VBA (WROX).
2. For information on ADO, download the Microsoft Data Access SDK.
3. Lookup the 'CopyFromRecordset' topic in the Excel help files.
4. You need to learn SQL - there are a lot of materal on the Internet: use
Google to find them.