Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excell VBA
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excell VBA
Resolved · Low Priority · Version 2003
Reply Fri 30 Jan 2009, 16:34Delegate Cameron said...
Cameron has attended:
Access VBA course
Access VBA course
Access Advanced course
Excel VBA Intro Intermediate course
Excell VBA
Is it possible to use SQL to query a range??
For upcoming training course dates see: Pricing & availability
RE: Excell VBA
Hi Cameron. SQL stands for "Structured Query Language" so it certainly is possible to use it to run queries on a range of data! SQL is a programming language itself and used to crunch data from all sorts of data sources, including Excel and Access.
Hope this helps,
Anthony
Reply Tue 21 Apr 2009, 10:13Delegate Cameron said...
RE: Excell VBA
So how would this process work?? As in where would the reference to a worksheet go etc.
"SELECT * FROM [workbook].[worksheet] WHERE ???? = 0"
I use SQL extensively in Access for creating recordsets, so I understand it quite well but as Excel is not really a database I have trouble understanding how it would work in Excel. Is it possible to get a simple example??
RE: Excell VBA
Hi Cameron. In VBA you need to use the Add method of the "QueryTables" collection object. Your example query suggest you're transferring data from Access to an Excel worksheet, but you can connect directly to an Access database from within other MS Office packages, although there are some stability (and coding!) issues. Here is a basic example lifted off the net:
Sub CreateQT()
Dim sConn As String
Dim sSql As String
Dim oQt As QueryTable
sConn = "ODBC;DSN=MS Access 97 Database;"
sConn = sConn & "DBQ=C:\Program Files\Microsoft Office\"
sConn = sConn & "Office\Samples\Northwind.mdb;"
sConn = sConn & "DefaultDir=C:\Program Files\Microsoft Office\Office\Samples;"
sConn = sConn & "DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
sSql = "SELECT Customers.CustomerID, Customers.CompanyName, Customers.City "
sSql = sSql & "FROM `C:\Program Files\Microsoft Office\Office\Samples\Northwind`"
sSql = sSql & ".Customers Customers "
sSql = sSql & "WHERE (Customers.City='Berlin') "
sSql = sSql & "ORDER BY Customers.CompanyName"
Set oQt = ActiveSheet.QueryTables.Add( _
Connection:=sConn, _
Destination:=Range("a1"), _
Sql:=sSql)
oQt.Refresh
End Sub
In the above case (which is attaching to an Access databse) your SQL query ends up as a string variable which is passed to the QueryTables object. The same thing applies for databases built in Excel, but coding connection is obviously much easier.
This is some basic code for the Excel VBA query:
http://www.dbforums.com/microsoft-excel/907427-export-sql-query-excel-vba-easier-way.html
...and this goes into more detail:
http://www.excelguru.ca/node/23
Hope this helps,
Anthony
Reply Tue 21 Apr 2009, 11:14Delegate Cameron said...
RE: Excell VBA
Excellent example. The layout of the sConn makes it very simple to understand.
|