| Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers. New ways to automate corporate reporting
Microsoft Excel 2000 provides several new Microsoft Visual Basic® for Applications (VBA) properties, methods, and objects that allow you to take advantage of new data access methods when you automate queries and PivotTable® reports in custom solutions. OLE DB support OLE DB is a new data access standard that provides access to multidimensional (OLE DB for OLAP) data sources, such as databases created by using Microsoft SQL Server OLAP Services, that aren't supported by the previous ODBC standard. (On-Line Analytical Processing (OLAP) is a way to organize large business databases to facilitate retrieval and reporting.) Unlike ODBC, which is designed primarily to provide access to SQL data, the OLE DB standard supports access to all types of OLE Component Object Model (COM) data, in addition to SQL data. ADO interface ActiveX® Data Objects (ADO) is an interface, built on OLE DB, that provides updated functionality similar to the DAO and RDO interfaces provided in previous Excel releases. ADO provides you with a set of high-level objects and methods for data access programming. Excel 2000 also supports the previous methods—ODBC, DAO, and RDO—so that you can continue to use programs you wrote for previous versions with Excel 2000. This article explains how to use both OLE DB and ADO to create queries and PivotTable reports, including example VBA programs that use each method to create each type of report. When to use the Excel programming model for OLE DB vs. ADOBecause both OLE DB and ADO provide similar capabilities, you can use either approach. OLE DB is designed for general-purpose use, and using Excel's built-in OLE DB support may result in simpler programs than using ADO. OLE DB also provides capabilities that are compatible with other versions of Excel. ADO has the advantages of low memory overhead and a small disk footprint, and is optimized for client/server and Web-based applications. The Excel OLE DB programming modelUse OLE DB when you want to access types of databases (OLE DB for OLAP) that you can't access via ODBC. You can also use OLE DB to access SQL data. The Excel 2000 OLE DB capability includes support for accessing ODBC-compatible databases. Limitations of OLE DB The Excel 2000 implementation of OLE DB does not support background refresh, parameter queries, or page fields that retrieve data for each item separately. Also, Microsoft Query 2000 can't construct OLE DB queries. Create an external data range by using OLE DBThe following program uses SQL Server OLE DB to import a database table into an Excel external data range. The SQL server database is named Bookstore, and has a table named Authors. The program retrieves all of the information from this table, using guest as the user ID and password, and imports the data into the current worksheet starting at cell A1.
Sub CreateOLEDBQueryTable()
'Declare variables
Dim objMyQueryTable As QueryTable
'Create external data range
Set objMyQueryTable = ActiveSheet.QueryTables.Add( _
Connection:="OLEDB;Provider=SQLOLEDB;Data
Source=Bookstore;User ID=guest;Password=guest", _
Destination:=ActiveSheet.Range("A1"))
'Retrieve data
With objMyQueryTable
.CommandText = "Select * From Authors"
.CommandType = xlCmdSQL
.Name = "Authors"
.Refresh False
End With
End Sub
You can also use a text file with extension .rqy to import data into an external data range. The following example .rqy file retrieves the same information from the Bookstore database as the previous program. When you open this file in Excel, Excel creates a new workbook containing the retrieved information in an external data range.
QueryType=OLEDB Version=1 Connection=Provider=SQLOLEDB;Data Source=Bookstore;User ID=guest;Password=guest; CommandType=SQL CommandText=Select * From Authors For OLAP databases, you can't return data to Excel as an external data range, only as a PivotTable report. See the following section for examples of retrieving data from OLAP databases. Create a PivotTable report by using OLE DBThe following example creates an Excel PivotTable report using the same Authors table from the Bookstore database described previously. The report includes the names of the authors and summarizes data from the contract field in the database.
Sub CreateOLEDBPivotTable()
'Declare variables
Dim objMyPivotCache As PivotCache
Dim objMyPivotTable As PivotTable
'Create PivotCache
Set objMyPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
'Retrieve data
With objMyPivotCache
.Connection = "OLEDB;Provider=SQLOLEDB;
Data Source=Bookstore;
User ID=guest;Password=guest"
.CommandText = "Select * From Authors"
.CommandType = xlCmdSQL
End With
'Create PivotTable
Set objMyPivotTable = ActiveSheet.PivotTables.Add( _
PivotCache:=objMyPivotCache,
_TableDestination:=Range("A1"), TableName:="Contract
Status of Authors")
objMyPivotTable.AddFields ("author_name")
objMyPivotTable.PivotFields("contract").Orientation = xlDataField
End Sub
The following example creates an Excel PivotTable report using data from an OLAP database named FoodMart located on Server3 (the FoodMart sample database is included with Microsoft SQL Server OLAP Services). The report displays the Store dimension field as a row field and the Store Sales measure field as a data field.
Sub CreateOLAPPivotTable()
'Declare variables
Dim objMyPivotCache As PivotCache
Dim objMyPivotTable As PivotTable
'Create PivotCache
Set objMyPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
'Retrieve data
With PC
.Connection = "OLEDB;Provider=MSOLAP.1;
Location=Server3;Connect Timeout=60;
Initial Catalog=FoodMart;Client Cache Size=25"
.CommandText = "Sales"
.CommandType = xlCmdCube
End With
'Create PivotTable
Set objMyPivotTable = ActiveSheet.PivotTables.Add( _
PivotCache:=objMyPivotCache, _
TableDestination:=Range("A1"), _
TableName:="OLAP PivotTable")
objMyPivotTable.CubeFields("[Measures].[Store Sales]").Orientation = xlDataField
objMyPivotTable.CubeFields("[Store]").Orientation = xlRowField
End Sub
You can also use a text file with extension .oqy to create a PivotTable report from OLAP data. The following example .oqy file creates a PivotTable report based on the FoodMart database. When you open this file in Excel, Excel creates a new workbook containing a blank PivotTable report with the dimensions and measures from the OLAP database available on the PivotTable toolbar for inclusion in the report.
QueryType=OLEDB Version=1 Connection=Provider=MSOLAP.1;Location=Server3;Connect Timeout=60;Initial Catalog=FoodMart;Client Cache Size=25; CommandType=Cube CommandText=Sales How to handle OLE DB errorsWhen an OLE DB failure occurs, Excel fetches the errors from the provider's OLE DB Error object, and stores them in the Application.OLEDBErrors collection. You can use the following code to retrieve and display error strings for OLE DB queries.
Set ErrObj = Application.OLEDBErrors.Item(1) MsgBox "The following error occurred:" & ErrObj.ErrorString The following table lists the OLE DB error strings. For more information about the OLE DB operations, see the Help or other documentation for your OLE DB provider.
Using ADOADO provides a consistent application-level interface to access various types of data. Limitations of ADO Use ADO for Excel 2000 data access programming. If your program must also run with earlier versions of Excel, you can use the earlier DAO, RDO, or ODBC approaches. Before you can write ADO programs, you must create a reference to the ADO library: In the Visual Basic Editor, click References on the Tools menu, and then select the Microsoft ActiveX Data Objects 2.1 Library check box. Create an external data range by using ADOThe following program uses ADO to import data from a database table into an Excel external data range. The SQL server database is named Bookstore, and has a table named Authors. The program retrieves the information from several fields in this table, using guest as the user ID and password, and imports the data into the current worksheet starting at cell A1.
Sub CreateQTfromADORecordset()
'Declare variables
Dim objMyConn As New ADODB.Connection
Dim objMyRecordset As New ADODB.Recordset
Dim objMyQueryTable As QueryTable
'Open Connection
obyMyConn.Open "Provider=SQLOLEDB;Data Source=Bookstore;User ID=guest;Password=guest;"
'Open Recordset
objMyRecordset.Open "SELECT Authors.ID, Authors.Name, Authors.Phone, Authors.Address, Authors.City, Authors.State, Authors.Zip, Authors.Contract FROM dbo.authors Authors", Conn, adOpenDynamic, adLockReadOnly, adCmdText
'Create external data range
Set objMyQueryTable = ActiveSheet.QueryTables.Add(objMyRecordset, Range("A1"))
objMyQueryTable.Refresh False
End Sub
Copy data into Excel by using ADOYou can use ADO to copy data directly to Excel, as well as to create external data ranges. The following example copies data from the Bookstore database onto an Excel worksheet. When you copy data in this manner, the Excel range isn't refreshable.
Sub GetDataFromADO()
'Declare variables
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset
'Open Connection
objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=Bookstore;User ID=guest; Password=guest;Prompt=Complete"
objMyConn.Open
'Set and Excecute SQL Command
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = "select * from dbo.Authors">
objMyCmd.CommandType = adCmdText
objMyCmd.Execute
'Open Recordset
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open objMyCmd
'Copy Data to Excel
ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)
End Sub
Create a PivotTable report by using ADOThe following example creates an Excel PivotTable report using the same Authors table from the Bookstore database described previously. The report includes the names of the authors and summarizes data from the contract field in the database.
Sub CreatePTfromADORecordset()
'Declare variables
Dim objMyConn As New ADODB.Connection
Dim objMyRecordset As New ADODB.Recordset
Dim objMyPivotCache As PivotCache
Dim objMyPivotTable As PivotTable
'Open Connection
objMyConn.Open "Provider=SQLOLEDB;Data Source=Bookstore;User ID=guest;Password=guest;"
'Open Recordset
objMyRecordset.Open "SELECT * FROM dbo.authors Authors", objMyConn, adOpenForwardOnly, adLockReadOnly, adCmdText
'Create PivotCache
Set objMyPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
Set objMyPivotCache.Recordset = objMyRecordset
'Create PivotTable
Set objMyPivotTable = ActiveWorkbook.Sheets("Sheet1").PivotTables.Add(objMyPivotCache, Cells(1, 1))
objMyPivotTable.AddFields ("author_name")
objMyPivotTable.PivotFields("contract").Orientation = xlDataField
'Close the ADO recordset/connection
objMyRecordset.Close
objMyConn.Close
End Sub
More information
|
Printer-Friendly Version
