|
QueryTables.Add
With VBA, you create queries using the Add method of the QueryTables
collection object. The Add method has these arguments (from help.)
expression.Add(Connection, Destination,
Sql)
Connection is a string that identifies the database, Destination is a Range
that defines the top, left corner of the QueryTable on your sheet, and Sql is a
string that defines the recordset that will populate the QueryTable.
The Connection String
The Connection argument is a string that tells the query table which database
to use. This string has many elements and must be precise. Here's an
example of a Connection string:
ODBC;DSN=MS Access 97 Database;DBQ=C:\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb;DefaultDir=C:\Program Files\Microsoft
Office\Office\Samples;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;
ODBC stands for Open DataBase Connectivity and is an interface to ODBC
drivers. Many applications use ODBC drivers to allow other programs to
access their data.
DSN stands for Data Source Name and stores information about the type of
database to which you want to connect.
DBQ is the path and name of the database to which you want to connect.
How to Create a Connection String
The easy answer is don't. A fast way to get the Connection string you
want, is to create the QueryTable manually (as described here) and look at its
Connection string. When you've created the QueryTable, open the VBE
(Alt-F11), show the Immediate Window (Cntrl-G), and look at the Connection
string for that table by printing its Connection property.
Now you can copy and paste that Connection string into your code.
How to Create an Sql String
If you know the syntax of the Sql you're using, you just type it in your
code. If not, and you really don't want to learn, you use the same method
as above. The property you access for the Sql string is the CommandText
property, as in:
?sheet1.QueryTables(1).CommandText
For versions of Excel prior to Office2000, the CommandText property was
called Sql. The Sql property still works in later versions, so if you're
developing across versions, Sql is safer.
Example: QueryTables.Add
One way to see the proper code for adding a QueryTable is to turn on the
macro recorder while you create one manually. The recorder will show you
the settings for every property of the QueryTable. That can be
instructive, but you probably will be happy with the defaults, so you can omit
them. The basics look like this:
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
Note that if you don't refresh, you will still be creating a QueryTable, but
you won't see it on your Worksheet.
A Word About Arrays and the Macro Recorder
When you turn on the macro recorder and add an External Data table, you will
get a code fragment that looks something like this:
This code fragment is cutoff on the right because it's really long.
Note where the Connection and CommandText properties are set in the arguments of
the Add method. The Connection property is actually an array of arrays.
I have no idea why this is. My guess is that there is a 255 character
limitation that this overcomes, and by making each element of the array under
the limit, some problem has been avoided. I've never experienced the
problem regardless of the length of the Connection string.
The CommandText argument similarly uses an array. But it's not an array
of arrays, it's just an array. Even if my guess is correct, I still don't
see why Connection needs to be nested arrays. When I use the Add method or
set Connection or CommandText, I don't use arrays. If you choose to use
arrays, the compiler will build the string from all the elements in the array in
whatever order they are.
|
