CHAPTER 2

Data In, Data Out

Excel 2007 provides us with a number of methods to bring in data. We can import or link to many different data sources, including Microsoft Access databases, SQL Server databases, text files, ODBC databases, and XML files, to name a few.

Excel's Data Import Tools

Excel 2007 has a rich set of data handling tools. On the Developer ribbon, you'll find quick access to Microsoft Access databases, web tables, and text files. Excel also includes data access to SQL Server and OLAP databases, XML data, and ODBC data sources.

By recording macros, you can see how Excel connects us to various data sources. From there, you'll begin writing your own data access routines.

---

Note The first few examples will run under Windows 2000 or Windows XP as is. Windows Vista still supports Visual Basic 6.0 (and by extension VBA), but does not ship with all of the data access components of its predecessors. To run the examples under Windows Vista, check the following link to Microsoft's Support Statement for Visual Basic 6.0 on Windows Vista: http://msdn2.microsoft.com/en-us/vbrun/ms788708.aspx.

---

Importing Access Data

Let's look at how Excel brings in external data by recording a quick macro to import data from an Access database.

Create a new macro named GetAccessData. We're going to import the Extended Employees list (query) from the Northwind 2007 database onto Sheet1 in a new Excel workbook.

1. Select the Data ribbon.
2. Select From Access from the Get External Data section of the Data ribbon.
3. Navigate to wherever you have the Northwind database stored.

---

Note The files for these examples can be found in the Source Code/Download section of this book's home page at www.apress.com.

---

---

Note There is a new version of Northwind in Access 2007 that uses a file extension of *.accdb for Access databases.

---

1. In the Select Table dialog box, choose Employees Extended, and click OK.
2. In the Import Data dialog box, you have choices of how you want to view the data (table, PivotTable, or PivotChart) and where you want to put the data, as well as advanced options. For now, just accept the defaults by clicking the OK button.

The code generated from this looks like Listing 2-1.

The SourceType and Source settings of the ListObject.Add method tell whether the data is from an Excel sheet (xlSrcRange = 1) or an external source (xlSrcExternal = 0). When the SourceType is external, the source is an array of string values specifying a connection to the source data.

Buried at the end of our lengthy source data string is this line of code:

Destination:=Range("$A$1")).QueryTable

A QueryTable object is a worksheet table that is created any time data is returned from an external data source like an Access or SQL Server database. Table 2-1 lists the members of the QueryTable object and describes them.

Table 2-1. QueryTable Object Members

QueryTable Object Members	Description
CommandType	Returns/sets one of the xlCmdType constants. The xlCommandType constants define whether an SQL statement, cube, or OLE DB data source will be requested. The default value is xlCmdSQL.
CommandText	Returns/sets the command string for the data source.
RowNumbers	True if row numbers are added as the first column of the query table.
FillAdjacentFormulas	True if formulas to the right of the query table are automatically updated whenever the query table is refreshed.
PreserveFormatting	True if formatting common to the first five rows of data are applied to new rows in the query table.
RefreshOnFileOpen	True if the PivotTable cache or query table is automatically updated whenever the workbook is opened.
BackgroundQuery	True if queries for the query table are performed in the background.
RefreshStyle	Returns/sets the way rows on the specified worksheet are added or deleted to accommodate the number of rows in a recordset (returned by a query).
SavePassword	True if password information in an ODBC connection string is saved with the query. False if the password is removed.
SaveData	True if data for the query table report is saved with the workbook. False if the report definition is saved and nothing else.
AdjustColumnWidth	True if the column widths are automatically adjusted for the best fit each time you refresh the specified query table. False if they are not.
RefreshPeriod	Returns/sets the number of minutes between refreshes.
PreserveColumnInfo	True if column sorting, filtering, and layout information is preserved when a query table is refreshed. Default value is False.
SourceDataFile	Returns/sets a String value that indicates the source data file for a query table.
ListObject.DisplayName	Property of ListObject. Creates or returns a named range for the inserted data.
Refresh	Causes Excel to connect to the data source, execute the SQL query again, and return data to the range that contains the QueryTable object. The QueryTable object doesn't communicate with the data source once data is inserted unless this method is called.

Simplifying the Code

The code Excel generates, while accurate, is certainly not something one would want to maintain. And you can forget about flexibility. The Array function used to pass in the connection string and database information is one scary looking piece of code. One of the first things we can do to simplify this is to create our own connection string and store it in a variable. This will give us the advantage of easier maintenance. Create a new function in Module1 and name it GetAccessData2. Paste the code from GetAccessData into it, and then add the following declaration and code (be sure to change the path to the Northwind 2007 database to your location):

Dim sConnString As String

    sConnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";"
    & "User ID=Admin;"
    & "Data Source=C:projectsExcel2007BookFilesNorthwind 2007.accdb;"
    & "Mode=Share Deny Write;Extended Properties="""";"
    & "Jet OLEDB:System database="""";"
    & "Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";"
    & "Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;"
    & "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;"
    & "Jet OLEDB:New Database Password="""";"
    & "Jet OLEDB:Create System Database=False;"
    & "Jet OLEDB:Encrypt Database=False;"
    & "Jet OLEDB:Don't Copy Locale on Compact=False;"
    & "Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;"
    & "Jet OLEDB:Support Complex Data=False"

This code is much more readable and there is less danger of breaking our code if we ever need to point to another Access data source.

Now we just need to change the Source property of the ListObjects.Add method to refer to the connection string in place of the array:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=sConnString,
        Destination:=Range("$A$1")).QueryTable

With a couple of quick and easy changes, we've made the Macro Recorder–generated code much easier to read and modify. Let's import the same data onto Sheet2 in the workbook:

1. Navigate to Sheet2 in the workbook.
2. Run the GetAccessData2 macro.

Oops, we've generated an error (see Figure 2-1).

Figure 2-1. Macro code generates error

Why should there be an error? Excel generated this code itself (with the exception of your addition of a string variable). Click the Debug button, and the VBE should show us the errant line of code (see Figure 2-2).

Figure 2-2. DisplayName property fires error

The ListObject.DisplayName property creates a named range on the worksheet. Even though we're working on Sheet2, a range named Table_Northwind_2007a.accdb already exists in this workbook. Easy enough to fix:

1. Click Debug.
2. Remove or change the a before the file extension, or simply choose another name entirely.
3. Press F5 to continue running the code.

There is still a lot of code stored on our sConnString variable. Many of the Jet database property values default to False, since we did a simple import of data. We can remove them from our connection string and leave just the essential information required to access our Northwind database. Create one last new method and name it GetAccessData3. Paste the code from GetAccessData2 into it and make the following changes:

sConnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";"
        & "User ID=Admin;"
        & "Data Source=C:projectsExcel2007BookFilesNorthwind 2007.accdb;"

We could also remove any property call from the QueryTable object's instantiation as well, to further simplify the code—but we'll leave that alone for now.

The GetAccessData, GetAccessData2, and GetAccessData3 subroutines show all three versions of this code with each version becoming more succinct than the last.

Importing Text Data

Before we begin writing our own code to import data, let's record one more macro to see some of the settings available when we bring in data from a text file.

1. Create a new workbook and name it DataAccessSample02.xlsm.
2. Create a new macro and name it GetTextData.
3. On the Data ribbon, choose From Text.
4. Navigate to the myfilepathmaillist.csv file, and then choose the Import command. The Text Import wizard will open, as shown in Figure 2-3.
   
   
   

   Figure 2-3. The Text Import wizard

The file is comma-delimited (the default selection in the Original Data Type section), so just click Next.

On Step 2 of the Text Import wizard, the default delimiter is Tab. The "Data preview" section should show us our columns separated by vertical lines. Since our file is not tab-delimited, the preview shows our raw data file (see Figure 2-4).

Figure 2-4. View of maillist.csv with Tab selected as delimiter

Select Comma from the Delimiters options. The data preview now shows your data in the correct columnar display (see Figure 2-5).

Figure 2-5. View of maillist.csv with Comma selected as delimiter

Click Next to continue to Step 3 of the wizard (see Figure 2-6), where we can choose the data type for each column of data.

Figure 2-6. Step 3 lets you choose data types for each column.

The onscreen prompt tells us that the General format will convert numeric values to numbers, date values to dates, and so on. We will choose each column in turn, and choose the Text data type for our data. The Phone Number column contains numbers, but we want Excel to treat them as text. The column heading in the "Data preview" window shows us the data type selected for each column.

Click Finish after applying the Text data type to all columns (see Figure 2-7).

Click OK to let Excel place the data beginning in cell A1 (see Figure 2-8).

Click cell A1, and then stop the Macro Recorder. Figure 2-9 shows the data after it has been imported from the CSV file.

On the Developer ribbon, click the Visual Basic command or press Alt+F11 to open the Visual Basic window. Let's take a look at the code Excel generated for us. We'll examine the differences between importing Access data and text data in the Macro Recorder.

Figure 2-7. "Data preview" window after applying the Text data type to all columns

Figure 2-8. Entering a location for the data

Figure 2-9. Data imported from maillist.csv

Macro Recorder–Generated Text Import Code

After we stop the Macro Recorder, we are left with code that looks like Listing 2-2.

One of the first differences to notice about this code when compared to the Access data import is how simple the connection string is. There is no complex Source string, and there are no Command object properties (CommandType and CommandText) to set. We simply tell Excel we're connecting to a text file, and then provide the path to the file and add it to the QueryTables collection via the Add method.

Then there are some common properties, such as the FillAdjacentFormulas and SavePassword properties. After the RefreshPeriod property, we begin to see a lot of text file–specific commands. We can set properties that define the type of text file we're working with by setting the TextFileParse type to xlFixedWidth if our data is arranged in columns of fixed widths, or xlDelimited if we have a character-delimited file. If we set this to xlDelimited, we can then set one or more of the following properties to True:

• TextFileTabDelimiter
• TextFileSemicolonDelimiter
• TextFileCommaDelimiter
• TextFileSpaceDelimiter

TextFileColumnDataTypes Property

The Macro Recorder generated this line of code:

.TextFileColumnDataTypes = Array(2, 2, 2)

Setting this property to 2 for all columns tells Excel to format the columns as text. These values correspond to the xlTextFormat constant in Table 2-2. If you enter more values into this array than there are columns in your data, the additional values are ignored. To see the numeric equivalent for Excel constants like these, type the name into the Immediate window (go to View Immediate Window or press Ctrl+G) in the VBE, preceded by the ? output character. You can use the xlColumnDataType constants listed in Table 2-2 to specify the column data types used or the actions taken during a data import.

Table 2-2. TextFileColumnDataTypes Enums

Constant	Description	Value
xlGeneralFormat	General	1
xlTextFormat	Text	2
xlSkipColumn	Skip column	9
xlDMYFormat	Day-month-year date format	4
xlDYMFormat	Day-year-month date format	7
xlEMDFormat	EMD date	10
xlMDYFormat	Month-day-year date format	3
xlMYDFormat	Month-year-day date format	6
xlYDMFormat	Year-day-month date format	8
xlYMDFormat	Year-month-day date format	5

A quick way to find the value of any of Excel 2007's built-in constants or enumerations is to type it into the Immediate window, preceded by a ? character. This will display the value as shown in Figure 2-10.

Figure 2-10. Viewing constant values in the Immediate window

We've seen that Excel's Macro Recorder is a fast and easy way to explore some of the properties and methods available when bringing data into Excel. Let's write a little of our own code and explore some flexible methods of data transfer. These methods will work in Excel or any other VB- or VBA-enabled application, making them relatively portable and reusable.

Using DAO in Excel 2007

Data Access Objects (or DAO, as it's commonly known) has been around Microsoft Office for many versions, going back to 1992, when Jet was introduced. DAO was the first data access tool available to VB and VBA programmers, and can still be used to manipulate data in older versions of Office and ODBC-compliant database systems.

DAO is very easy to use, and you've probably encountered DAO code if you've done any work in versions of Access preceding the 2000 release, when it was the default data access tool. In Office 2000, Microsoft made ADO the default data access method, which caused programmers who used DAO heavily to learn to use explicit references to their data access model.

You can use DAO to access SQL data via ODBC, and Microsoft Access data via Jet. Jet is no longer a part of the Microsoft Data Access Components (MDAC) with the 2007 release. Office 2007 introduces a new version of the Jet engine called ACE (Access Engine).

The DAO object model is shown in Figure 2-11, and its common objects are described in Table 2-3, which follows.

Figure 2-11. DAO Jet object model

Table 2-3. Common DAO Objects

Object	Description
DBEngine	The top-level object in the DAO object hierarchy
Workspace	An active DAO session
Connection	The network connection to an ODBC database
Database	An open database
Error	Data access error information
Field	A field in a database object
TableDef	Saved table information in a database
QueryDef	Saved query information in a database
Recordset	A set of records defined by a table or query
Index	Table index
User	A user account in the current workgroup
Parameter	Query parameter
Property	Property of an object

Let's take a look at how easy DAO is to use by bringing data from the Northwind 2007 database into an Excel worksheet using DAO.

DAO Example 1: Importing Access Data Using Jet

Open a new workbook and name it DataAccessSample03.xlsm. Be sure to use the .xlsm extension so your workbook is macro-enabled.

Open the VBE by choosing the Visual Basic command from the Developer ribbon or by pressing Alt+F11.

Before we can retrieve data using DAO, we must add a reference to the DAO library in our project.

1. Select Tools References in the VBE.
2. Find the Microsoft DAO 3.6 Object library in the list, and select it, as shown in Figure 2-12.
3. Click OK.
4. Insert a new standard module by selecting Insert Module.
5. Create a new subroutine called GetDAOAccessJet().
6. Add the following variable declarations:
   
   Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlSheet As Worksheet
Dim i As Integer
Dim arr_sPath(1) As String
   
   
   

   Figure 2-12. Adding a reference to the DAO library

We're declaring the db and rs variables to hold our database and recordset objects. The xlSheet variable will provide a simpler way to refer to the worksheet we'll be populating with data. We're going to store the path to two versions of the Northwind database—the new version with the .accdb extension and the Access 2000 version with the .mdb extension—to compare how DAO works with these.

Add the following code to set up the file paths and Excel worksheet (be sure to change the paths to the database files to reflect your location):

arr_sPath(0) = "C:projectsExcel2007BookFiles orthwind 2007.accdb"
arr_sPath(1) = "C:projectsExcel2007BookFiles orthwind.mdb"

Set xlSheet = Sheets("Sheet1")
xlSheet.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select

We're assigning the Sheet1 object from our workbook to the variable xlSheet to provide easier access to that sheet. This eliminates the need to type Sheets("Sheet1") whenever we need to reference the worksheet we're manipulating.

Next we'll instantiate our database and recordset objects:

Set db = Workspaces(0).OpenDatabase(arr_sPath(0), ReadOnly:=True)
Set rs = db.OpenRecordset("Employees")

This code creates the default Jet workspace and fills a recordset with the information in the Employees table in the Northwind 2007 database.

Now we'll fill the first row in the worksheet with the field names from the recordset and add bold formatting to the column headings:

For i = 0 To rs.Fields.Count - 1
   xlSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
Next i

xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, rs.Fields.Count))
     .Font.Bold = True

Rather than create a loop to walk through the recordset and populate the sheet row by row and column by column, we'll use Excel's CopyFromRecordset method to fill the sheet with data:

xlSheet.Range("A2").CopyFromRecordset rs

The last thing we'll do before inserting our cleanup code is adjust the column widths to show the full text values (using the AutoFit method):

xlSheet.Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select

The first call to Range("A1").Select puts the cursor within the region we want to work with (in case there's more than one area with data on your worksheet). The next line, Selection.CurrentRegion.Select, selects any contiguous area of cells based on the current cursor location. Next comes our AutoFit command, followed by the selection of a single cell (to remove the selection from the entire range).

The entire function should now look like Listing 2-3.

Let's run our code and see the result on Sheet1.

1. On the Developer ribbon, choose the Macros command.
2. Select the GetDAOAccessJet macro from the list, and click the Run button. DAO generates an error, as shown in Figure 2-13.
   
   
   

   Figure 2-13. Unrecognized database format error

3. Click the Debug button, and notice that our attempt to instantiate our DAO.Database object is failing (see Figure 2-14).
   
   
   

   Figure 2-14. OpenDatabase method fires error

DAO Jet, it seems, does not support the new Access database format. Does this mean we cannot use DAO with *.accdb files? No, it does not. In a short while, we'll take a look at how we can access data from Access 2007 using DAO with ODBC. For now, let's continue with Jet.

To make this code work, all we have to do is change the array index in our arr_sPath variable from 0 to 1.

Set db = Workspaces(0).OpenDatabase(arr_sPath(1), ReadOnly:=True)

Rerun the code, and your worksheet should look like Figure 2-15.

Figure 2-15. The Employees table from the Access 2000 version of the Northwind database

---

Note According to the help file, the Range.CopyFromRecordset method will fail if the DAO (or ADO) recordset contains an OLE object. This seems to be true only sometimes. In the preceding example, we filled a DAO recordset object with the entire contents of the Employees table from the Access 2000 version of the Northwind database. This table includes a field named Photo that does contain an OLE object and is included in the data returned to us.

---

Using the CopyFromRecordset method is much more efficient and more performant than looping through a recordset to retrieve the entire contents.

---

Note When using the Range.CopyFromRecordset method, copying begins at the current row of the recordset object. After copying is completed, the EOF property of the recordset object is True. If you need to reuse your recordset, you must call its MoveFirst method (if the type of recordset you've created is not using a forward-only cursor).

---

DAO Example 2: Importing Access Data Using ODBC

In the previous example, you saw that Jet 4 does not support the *.accdb format, and you learned that it is no longer a part of the MDAC. How can you use DAO to access data in the new Access database format? The answer is ODBC (Open Database Connectivity).

The DAO ODBC object model is shown in Figure 2-16.

Figure 2-16. DAO ODBC object model

The method for importing data using DAO ODBC is somewhat different than using Jet. In Jet, we could use a database object to refer to our Access database. Using ODBC, we have to create Workspace and Connection objects that we'll use to connect to the database and retrieve a recordset of data.

In the VBE, on the same code module, add a subroutine called GetDAOAccess2007ODBC(). Add the following variable declarations:

Dim wrk As DAO.Workspace
Dim cnn As DAO.Connection
Dim rs As DAO.Recordset
Dim sConn As String
Dim xlSheet As Worksheet
Dim iFieldCount As Integer
Dim i As Integer
Dim arr_sPath(1) As String

This looks very similar to our last example, but let's look at the differences. We've added variables to hold our Workspace and Connection objects, as previously noted. We've also added the sConn variable to hold our connection string. This is where we'll tell our Connection object where to find the data we require. The last difference is that we've added a variable, iFieldCount, to hold the number of fields in our Recordset object.

Copy and paste the path string and worksheet setup code from the previous example:

'store path to Access 2007 and 2000 versions of Northwind db
arr_sPath(0) = "C:projectsExcel2007BookFiles orthwind 2007.accdb"
arr_sPath(1) = "C:projectsExcel2007BookFiles orthwind.mdb"

Set xlSheet = Sheets("Sheet1")
xlSheet.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select

Set the connection string:

sConn = "ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
            & "DBQ=" & arr_sPath(0)

Instantiate the Workspace and Connection objects:

Set wrk = CreateWorkspace("", "", "", dbUseODBC)
Set cnn = wrk.OpenConnection("", , , sConn)

We use the Workspace object's OpenConnection method to create the Connection object.

Next we'll use the Connection object's OpenRecordset method to fill our recordset with data from the Employees table:

Set rs = cnn.OpenRecordset("SELECT * FROM Customers", dbOpenDynamic)

Insert our column headings using the iFieldCount variable:

iFieldCount = rs.Fields.Count
For i = 1 To iFieldCount
    xlSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next i

xlSheet.Range(xlSheet.Cells(1, 1), _
                 xlSheet.Cells(1, rs.Fields.Count)).Font.Bold = True

Our first example used a zero-based counter to do this job:

For i = 0 To rs.Fields.Count - 1
   xlSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
Next i

The only real difference in this code is that we've assigned the rs.Fields.Count property to a variable in the new version. This is a bit more efficient because it eliminates the need to query the Recordset object for its Fields.Count with each pass through the loop. It does, however, change the way we reference our index values. In the first example, our loop refers to Fields.Count - 1; in the second, it simply refers to Fields.Count; and so on.

The remainder of the code is the same as the first example, with the addition of cleanup code for our new Workspace and Connection objects. The entire new subroutine looks like Listing 2-4.

Let's run this code from Sheet1 and see what it does.

1. Choose the Macros command from the Developer ribbon.
2. Select the DAOAccess2007ODBC macro from the list, and click Run. This should generate an error, as shown in Figure 2-17.
   
   
   

   Figure 2-17. DAO ODBC runtime error

3. Click the Debug button, and let's see where the code is stopping (see Figure 2-18).
   
   
   

   Figure 2-18. CopyFromRecordset stops the code.

Now we run into the error that I mentioned in the previous example. Excel's CopyFromRecordset method doesn't like the data type of a field or fields that we're returning in the recordset being passed to it. A look at the Northwind 2007 Customers table in Design view (Figure 2-19) will show us the data types in use here.

Figure 2-19. Northwind Customers table Design view

Figure 2-19 shows us that most of these fields use the Text data type, but we see a few that do not. You'll recall me mentioning that the Excel help file noted that OLE fields would cause the CopyFromRecordset method to fail, yet there are no OLE fields present here. The Memo, Hyperlink, and Attachment data types will all cause the CopyFromRecordset method to fail. To check, you could change your SQL statement in the OpenRecordset call to any of these:

SELECT Address FROM Customers

or

SELECT [Web Page] FROM Customers

or

SELECT Attachments FROM Customers

A recordset that includes any of these filters will cause our subroutine to fail. So let's then modify our SQL statement to include only those fields that are not of these data types.

Set rs = cnn.OpenRecordset("SELECT ID, Company, [Last Name],"
                        & " [First Name], [E-mail address], [Job title],"
                        & " [Business Phone], [Mobile Phone], [Fax Number],"
                        & " city, [state/province], [zip/postal code],"
                        & " [country/region] "
                        & "FROM Customers Order By Company", dbOpenDynamic)

Run the code, and your result should look like that in Figure 2-20.

Figure 2-20. DAO ODBC result from Northwind 2007 Customers table

Can you access data in other versions of Access using DAO ODBC? Yes, you can. With a simple edit to the GetDAOAccess2007ODBC subprocedure, you could use an ODBC call.

Change the connection string to reference the Access 2000 version file path by changing the 0 to 1 in the arr_sPath array:

sConn = "ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
            & "DBQ=" & arr_sPath(1)

Then use the original SQL statement in the call to OpenRecordset:

Set rs = cnn.OpenRecordset("SELECT * FROM Customers", dbOpenDynamic)

The Access 2000 version of the Northwind Customers table does not contain any of these issue-bearing data types, so we are able to query using Select * syntax.

DAO Example 3: Importing SQL Data Using ODBC

The final example of using DAO to bring data into your Excel project will focus on getting data from an SQL server (or other ODBC-compliant database). The process is identical to what we just did in our previous example, with the exception of a new connection string:

sConn = "ODBC;DATABASE=msdb;DSN=mySQL"

We're still using the ODBC reference in the string, but now we're passing in the database name and the DSN name. Here's the complete code. (You must reference a valid database and DSN for this to provide you with output.)

Sub GetDAOSQLODBC()
Dim wrk As DAO.Workspace
Dim cnn As DAO.Connection
Dim rs As DAO.Recordset
Dim sConn As String
Dim xlSheet As Worksheet
Dim iFieldCount As Integer
Dim i As Integer

    Set xlSheet = Sheets("Sheet1")
    xlSheet.Activate
    Range("A1").Activate
    Selection.CurrentRegion.Select
    Selection.ClearContents
    Range("A1").Select

    sConn = "ODBC;DATABASE=msdb;DSN=mySQL"

    Set wrk = CreateWorkspace("", "", "", dbUseODBC)
    Set cnn = wrk.OpenConnection("", , , sConn)
    Set rs = cnn.OpenRecordset("SELECT * FROM msdbms", dbOpenDynamic)

    iFieldCount = rs.Fields.Count
    For i = 1 To iFieldCount
        xlSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
    Next i

    xlSheet.Cells(2, 1).CopyFromRecordset rs

    xlSheet.Select
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Columns.AutoFit
    Range("A1").Select


    'close workspace
    wrk.Close

    'release objects
    Set xlSheet = Nothing
    Set rs = Nothing
    Set wrk = Nothing
    Set cnn = Nothing
End Sub

Using ADO in Excel 2007

ActiveX Data Objects (ADO) was introduced by Microsoft in 1996 and has become the successor to DAO. Its database access technology is OLE DB (Object Linking and Embedding Database), which is the successor to ODBC.

The latest version of ADO is ADO 2.8. ADO lets us access, edit, and update data from many data sources by interfacing to these data sources via OLE DB providers. OLE DB providers speak to the database engine more directly than ODBC, and provide us with better performance.

In the examples in the previous section, we used DAO to interact with an Access 2007 database and an SQL database. You'll recall we could not interface with Access 2007 directly with Jet, but we could interact using ODBC. In both cases, DAO goes through Jet, then from Jet to ODBC, and then to the data engine. Then our data comes back. As you might imagine, this may not be the speediest route to your data. ADO, on the other hand, talks directly to your OLE DB provider, which speaks directly to the data engine, and vice versa. This is a much more direct route and provides better performance. ADO also gives us many settings to help fine-tune how we interact with our data. We can choose to run our cursor on the server (in a connected environment) or on the client (if the database supports it, in a disconnected environment).

I mentioned my use of explicit reference to DAO and ADO in my variable declarations earlier. This is due to the fact that Microsoft made ADO the default data mechanism with the release of Access 2000. Up until that time, Dim rs As Recordset meant a DAO recordset object to Access and nothing else. After the release of Access 2000, that same line of code referred to an ADO recordset. Having started out in life as an Access developer, I relied heavily on DAO in many of my applications. After upgrading to Access 2000 and beginning to use ADO (along with DAO), I learned to make my declarations complete to avoid confusing the compiler (not to mention debugging!).

In any application that uses both access protocols, explicitly creating your objects eliminates any confusion. Your application will always know the difference between an object declared as DAO.Recordset and one declared as ADODB.Recordset. If you do not explicitly declare your DAO or ADO objects, whichever object is higher in the references list will get priority. If the ADO 2.8 library is listed above the DAO 3.6 library, then any object declared as type Recordset will default to the ADO library.

ADO Example 1: Importing SQL Data

For our first ADO example, we're going to use the AdventureWorks sample database provided by Microsoft. You can install a copy of the AdventureWorks database by running the file AdventureWorksDB.msi.

You will be using SQL Server 2005 Management Studio Express to view the various database objects. To install Management Studio Express, run SQLServer2005_SSMSEE.msi.

1. Open a new workbook and name it DataAccessSample04.xlsm.
2. Before we begin using ADO in Excel 2007, we must add a reference to the ADO 2.8 library (see Figure 2-21).
   1. In the VBE, choose Tools References.
   2. Select the Microsoft ActiveX Data Objects 2.8 library.
   3. Click OK.
      
      
      

      Figure 2-21. Adding a reference to the ADO 2.8 library

If you have SQL Server 2005 installed on your machine, you can use that instead of SQL Server 2005 Management Studio Express.

For our first example, we'll be using a parameterized stored procedure to return a list of Adventure Works employees for a selected manager. We'll enter the manager's employee ID and retrieve a list of that manager's direct and indirect reports.

The AdventureWorks database contains a stored procedure called uspGetManagerEmployees. If we expand that item in the Stored Procedures tree, we see that it takes one parameter, ManagerID, which is of the Integer data type (as shown in Figure 2-22).

Figure 2-22. Parameterized stored procedure in AdventureWorks database, as viewed in SQL Server 2005 Management Studio Express

1. In the VBE, add a standard module.
2. Create a new subroutine called GetManagerEmployeeListSQL.
3. Add the following variable declarations:
   
   Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim xlSheet As Worksheet
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim i As Integer

We're using a few ADO objects to retrieve our data: an ADO Connection object to connect to the data, an ADO Command object to run our stored procedure, an ADO Parameter object to pass the ManagerID data to the stored procedure, and an ADO Recordset object to hold the results of our stored procedure.

In this example we are going to use cell A1 to hold the ManagerID information for our stored procedure's parameter. Let's add a modified version of the code we've been using to set up and clear our Excel worksheet:

Set xlSheet = Sheets("Sheet1")
xlSheet.Activate
Range("A3").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select

Although this looks very similar to the code used in the DAO examples, the third line, Range("A3").Activate, has changed. The DAO examples activated cell A1 to clear the entire current region on the worksheet. Since we're using cell A1 as input to our stored procedure in this example, we want to start clearing the contiguous range beginning at cell A3 instead.

Let's open our connection and assign it to a Command object:

Set cnn = New ADODB.Connection
sConnString = "Provider=SQLNCLI;Server=MyServerNameSQLEXPRESS;"
                & "Database=AdventureWorks;Trusted_Connection=yes;"
cnn.Open sConnString

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn

---

Note To connect to a named instance of SQL Server, the convention is to use a server name of the format <servername><instancename>. Note the way the Server property is set in our previous example: Server=MyServerNameSQLEXPRESS.

---

Now let's create our Parameter object, fill some of its properties, and add it to our Command object.

Set param = New ADODB.Parameter
With param
    .Name = "ManagerID"
    .Type = adInteger
    .Value = ActiveSheet.Range("A1").Value
End With

With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "uspGetManagerEmployees"
    .Parameters.Append param
End With

We are setting the Parameter object's Name property to ManagerID, as called for by the stored procedure, and telling it to use the Integer data type. Finally, we set its Value property to whatever value is contained in the active sheet's cell A1.

Once that's done, we set up our Command object by telling it what kind of command we need (stored procedure), and the name of the stored procedure. Then we append our Parameter object to the Command object's Parameters collection.

Table 2-4 gives a list of ADO data type enums, along with their actual values and the corresponding Access and SQL data types they refer to.

Table 2-4. ADO Data Types

Data Tyep	Value	Access	SQL Server
adBigInt	20		BigInt (SQL Server 2000 +)
adBinary	128		Binary TimeStamp
adBoolean	11	YesNo	Bit
adChar	129		Char
adCurrency	6	Currency	Money SmallMoney
adDate	7	Date	DateTime
adDBTimeStamp	135	DateTime (Access 97 [ODBC])	DateTime SmallDateTime
adDecimal	14
adDouble	5	Double	Float
adGUID	72	ReplicationID (Access 97 [OLEDB]), (Access 2000 [OLEDB])	UniqueIdentifier (SQL Server 7.0+)
adIDispatch	9
adInteger	3	AutoNumber Integer Long	Identity (SQL Server 6.5) Int
adLongVarBinary	205	OLEObject	Image
adLongVarChar	201	Memo (Access 97) Hyperlink (Access 97)	Text
adLongVarWChar	203	Memo (Access 2000 [OLEDB]) Hyperlink (Access 2000 [OLEDB])	NText (SQL Server 7.0 +)
adNumeric	131	Decimal (Access 2000 [OLEDB]	Decimal Numeric
adSingle	4	Single	Real
adSmallInt	2	Integer	SmallInt
adUnsignedTinyInt	17	Byte	TinyInt
adVarBinary	204	ReplicationID (Access 97)	VarBinary
adVarChar	200	Text (Access 97)	VarChar
adVariant	12		Sql_Variant (SQL Server 2000 +)
adVarWChar	202	Text (Access 2000 [OLEDB]	NVarChar (SQL Server 7.0 +)
adWChar	130		NChar (SQL Server 7.0 +)

The remainder of our code is basically identical to our previous examples. Listing 2-5 shows what the finished subroutine looks like.

Note that our cleanup code also refers to cell A3 when setting up the worksheet with the AutoFit method.

We can test this code out by entering a ManagerID in cell A1 on Sheet1 and running the GetManagerEmployeeListSQL method from the macro list.

1. Enter 16 in cell A1.
2. Choose GetManagerEmployeeListSQL from the macro list and run the code. The results are shown in Figure 2-23.
   
   
   

   Figure 2-23. Result of GetManagerEmployeeListSQL code

3. Enter a manager ID of 21 in cell A1 and run the code again. You'll see a longer list of employees since this is a higher-level manager.
4. Enter a manager ID of 16 again to see the setup code at work, clearing the used cells for the next round of data import.

ADO Example 2: Importing SQL Data Based on a Selection

In this exercise, we'll see how we can use Excel to generate a list, and how by making a selection from that list we can view detailed information about the selected item.

Adventure Works management wants to see a quick view of their reporting tree by manager. We're going to create a list of managers and then add code that will show the selected manager's reporting structure.

On Module1, add a new subroutine and name it GetManagerList. Add the following variable declarations:

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim xlSheet As Worksheet
Dim sConnString As String
Dim sSQL As String

Our setup code is very similar to our last example, except that we are going to put our list of managers on Sheet2. Our opening line of setup code will now look like this:

Set xlSheet = Sheets("Sheet2")

The remainder of the code is the same, with the obvious exception of the SQL statement. The SQL statement to generate our manager list looks like this:

sSQL = "SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName,"
        & " Person.Contact.LastName FROM Person.Contact"
        & " INNER JOIN HumanResources.Employee"
        & " ON Person.Contact.ContactID = HumanResources.Employee.ContactID"
        & " WHERE (((HumanResources.Employee.EmployeeID) In"
        & " (SELECT  HumanResources.Employee.ManagerID"
        & " FROM HumanResources.Employee)));"

Let's dissect this SQL statement a bit. Our manager list will show the employee ID as well as the first and last name for each manager. As you can see, the data is stored in two tables. The HumanResources.Employee table stores the EmployeeID field and the Person.Contact table stores the name fields.

The two tables have a common field, ContactID, that is used to join the tables in this query. Notice the WHERE clause, which contains a SELECT statement within it. This is known as nested SQL or an SQL subquery. Essentially, it says, "Only show us those employees whose employee ID can be found in the result of the subquery that contains only manager IDs." Subqueries such as this are a nice way to avoid creating temporary tables or individual queries to narrow down our search.

Here's the complete GetManagerList code:

Sub GetManagerList()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim xlSheet As Worksheet
Dim sConnString As String
Dim sSQL As String
    Set xlSheet = Sheets("Sheet2")
    xlSheet.Activate
    Range("A1").Activate
    Selection.CurrentRegion.Select
    Selection.ClearContents
    Range("A1").Select

    Set cnn = New ADODB.Connection
    sConnString = "Provider=SQLNCLI;Server=MyServerNameSQLEXPRESS;"
                    & "Database=AdventureWorks;Trusted_Connection=yes;"

    cnn.Open sConnString

    sSQL = "SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName,"
            & " Person.Contact.LastName FROM Person.Contact"
            & " INNER JOIN HumanResources.Employee"
            & " ON Person.Contact.ContactID = HumanResources.Employee.ContactID"
            & " WHERE (((HumanResources.Employee.EmployeeID) In"
            & " (SELECT  HumanResources.Employee.ManagerID"
            & " FROM HumanResources.Employee)));"

    Set rs = New ADODB.Recordset

    rs.Open sSQL, cnn, adOpenDynamic

    Sheets("Sheet2").Activate
    Range("A1").CopyFromRecordset rs

    xlSheet.Select
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Columns.AutoFit
    Range("A1").Select

    rs.Close
    cnn.Close

    Set rs = Nothing
    Set cnn = Nothing
    Set xlSheet = Nothing
End Sub

Run the code, and your result on Sheet2 should look like Figure 2-24.

Figure 2-24. The manager list displayed

Now that we have our list of managers, let's write the code to show the selected manager's staff.

Add a new subroutine to Module1 and name it GetSelectedManagerEmployeeListSQL.

Since this code is very similar to GetManagerEmployeeListSQL, take a look at Listing 2-6, which shows the entire code set, and we'll review the differences.

When a manager is selected and this code is run, it will generate the employee list on Sheet3. The manager's name will appear at the top of the page in cell A1, and the employee list will populate below it. We've added a couple of variables to our declarations:

Dim iMgrID As Integer
Dim sMgrName As String

These will hold the ID for our search and the name for our display.

We're setting our xlSheet variable to refer to Sheet3:

Set xlSheet = Sheets("Sheet3")

And we're pointing back to Sheet2 to get our selected manager information:

Sheets("Sheet2").Activate

We've added calls to two helper functions, GetMgrID and GetMgrName. These functions refer to the active sheet, so this line of code is important. We could optionally have made explicit references to Sheet2 in our functions or passed in the worksheet as an argument to the functions.

Add these functions to Module1.

Function GetMgrID() As Integer
Dim iReturn As Integer
Dim rngMgrID As Range

    Set rngMgrID = Cells(ActiveCell.Row, 1)
    iReturn = rngMgrID.Value
    Set rngMgrID = Nothing

    GetMgrID = iReturn
End Function
Function GetMgrName() As String
Dim sReturn As String
Dim iRow As Integer

    iRow = ActiveCell.Row
    sReturn = Cells(iRow, 2).Value & " " & Cells(iRow, 3).Value

    GetMgrName = sReturn
End Function

These functions illustrate two methods for referring to cells on Sheet2. GetMgrID uses a variable of type Range to refer to the cell in the current row and column 1. GetMgrName uses direct references to the cells by using the Cells object.

Let's test the code. On Sheet2, put your cursor in any column on a row containing manager information, as in Figure 2-25.

Figure 2-25. Selecting a manager

In the Macro window, run the GetSelectedManagerEmployeeListSQL subroutine, the results of which are shown in Figure 2-26.

Figure 2-26. Results of manager's employee search

ADO Example 3: Updating SQL Data

Now it's time to let Excel 2007 do some real work. We've seen a few different methods of retrieving data. Let's see what we can do to provide some updating capabilities to our worksheets.

In this example, we will import a list of employees with some personal data ("personal data" as defined by the AdventureWorks database; I don't know that many of us would agree that this meets our definition). Once we have that list, we'll create a routine that lets us update any information that has changed.

1. Open a new workbook and name it DataAccessSample05.xlsm.
2. In the VBE, add a new standard module.
3. Create a function named GetEmpList.
4. Add the following code:
   
   Sub GetEmpList()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim xlSheet As Worksheet
Dim sConnString As String
Dim sSQL As String
Dim i As Integer

    Set xlSheet = Sheets("Sheet1")
    xlSheet.Activate
    Range("A1").Activate
    Selection.CurrentRegion.Select
    Selection.ClearContents
    Range("A1").Select

    Set cnn = New ADODB.Connection
    sConnString = "Provider=SQLNCLI;Server=MYSERVERNAMESQLEXPRESS;"
                    & "Database=AdventureWorks;Trusted_Connection=yes;"

    cnn.Open sConnString
    sSQL = "SELECT emp.EmployeeID, Person.Contact.FirstName, "
            & "Person.Contact.LastName, emp.NationalIDNumber, "
            & "emp.BirthDate, emp.MaritalStatus, emp.Gender "
            & "FROM HumanResources.Employee AS emp "
            & "INNER JOIN Person.Contact ON emp.ContactID = "
            & "Person.Contact.ContactID"

    Set rs = New ADODB.Recordset
    rs.Open sSQL, cnn, adOpenDynamic

    For i = 1 To rs.Fields.Count
        ActiveSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
    Next i

    xlSheet.Range(xlSheet.Cells(1, 1), _
        xlSheet.Cells(1, rs.Fields.Count)).Font.Bold = True

    ActiveSheet.Range("A2").CopyFromRecordset rs
    xlSheet.Select
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Columns.AutoFit
    Range("A1").Select


    rs.Close
    cnn.Close


    Set rs = Nothing
    Set cnn = Nothing
End Sub

   This should be fairly standard code by now. We're setting up our worksheet, opening our ADO Connection object, filling a recordset with employee personal data from our SQL statement, and then displaying it on the worksheet.

---

Note All of our examples require a reference to the Microsoft ActiveX Data Objects 2.8 library.

---

1. Run the code and show the employee personal information data (see Figure 2-27).
   
   
   

   Figure 2-27. Employee personal data list

The AdventureWorks database comes with a stored procedure called HumanResources. uspUpdateEmployeePersonalInfo that will update this information (see Figure 2-28).

Figure 2-28. uspUpdateEmployeePersonalInfo and parameters

We are going to write a procedure called UpdateEmpPersonalInfo that will call this stored procedure and update the database with the information from the currently selected row in our Excel worksheet.

Before we begin coding this procedure, note that this stored procedure has five input parameters. Our earlier GetSelectedManagerEmployeeListSQL procedure called a stored procedure that took one parameter, which we instantiated and filled, and then appended to a Command object within the procedure, like so:

Dim param As ADODB.Parameter
'Code omitted...

    Set param = New ADODB.Parameter
    With param
        .Name = "ManagerID"
        .Type = adInteger
        .Value = iMgrID
    End With

    With cmd
        .CommandType = adCmdStoredProc
        .CommandText = "uspGetManagerEmployees"
        .Parameters.Append param
    End With

We could declare five variables of ADODB.Parameter type and repeat the Set param =... and the With...End With block five times from within our procedure—but that would make the code for this otherwise simple subroutine somewhat lengthy (the coders dictate of keeping routines to what can be seen on one monitor screen comes into play here). What we can do instead is use a VBA Collection object that we'll fill with Parameter objects (through a helper function), and that will then be appended to an ADO Command object.

1. On Module1, create a new subroutine named UpdateEmpPersonalInfo.
2. Add the following variable declarations:
   
   Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim colParams As Collection
Dim sConnString As String
Dim i As Integer
   
   
3. Insert the following code to activate the data worksheet and set up the Connection and Command objects:
   
   Sheets("Sheet1").Activate 'make sure we're on the data sheet

Set cnn = New ADODB.Connection
sConnString = "Provider=SQLNCLI;Server=MYSERVERNAMESQLEXPRESS;"
                & "Database=AdventureWorks;Trusted_Connection=yes;"
cnn.Open sConnString

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
4. 
   
   Next, fill the colParams collection with ADODB.Parameter objects:
   
   Set colParams = SetParams(ActiveCell.Row)

The SetParams function returns a filled collection and looks like this:

Function SetParams(RowNum As Integer) As Collection
'returns a collection of filled ADO Parameter objects
Dim colReturn As Collection
Dim prm As ADODB.Parameter

    Set colReturn = New Collection
    Set prm = New ADODB.Parameter
    With prm
        .Name = "EmployeeID"
        .Type = adInteger
        .Value = Cells(RowNum, 1).Value
    End With
    colReturn.Add prm

    Set prm = New ADODB.Parameter 'wipe prm and start over; best way to
                                    prevent leftover data
    With prm
        .Name = "NationalIDNumber"
        .Type = adLongVarWChar
        .Size = 15
        .Value = Cells(RowNum, 4).Value
    End With

    colReturn.Add prm

    Set prm = New ADODB.Parameter
    With prm
        .Name = "BirthDate"
        .Type = adDBTimeStamp
        .Value = Cells(RowNum, 5).Value
    End With
    colReturn.Add prm
    Set prm = New ADODB.Parameter
    With prm
        .Name = "MaritalStatus"
        .Type = adWChar
        .Size = 1
        .Value = Cells(RowNum, 6).Value
    End With
    colReturn.Add prm

    Set prm = New ADODB.Parameter
    With prm
        .Name = "Gender"
        .Type = adWChar
        .Size = 1
        .Value = Cells(RowNum, 7).Value
    End With
    colReturn.Add prm

    Set prm = Nothing
    Set SetParams = colReturn
End Function

There is nothing really fancy going here, although we have called upon a new property of the Parameter object. We're instantiating the Parameter object with this line of code:

Set prm = New ADODB.Parameter

Then we are setting various properties. You might have noticed when looking at the parameters list in SQL Server that some parameters were numeric and others were various flavors of char (nchar and nvarchar, to be exact). These parameters require an additional property setting, the Parameter.Size property. You also have other properties available, such as the Direction property, which you can set to determine whether a value is for input or output.

With prm
    .Name = "EmployeeID"
    .Type = adInteger
    .Value = Cells(RowNum, 1).Value
End With
colReturn.Add prm

Once the properties are set, we add the prm variable to our colReturn collection.

We reuse the prm variable by reinstantiating it before setting the next set of properties and adding to the collection. This is an effective way of reusing an object and ensures you don't have any "leftover" property settings lingering.

This process is repeated for each input parameter that uspUpdateEmployeePersonalInfo requires us to provide. Finally, we set the function's return value to the internal collection object:

Set SetParams = colReturn

Next, we'll finish setting up the Command object and loop through the collection of Parameter objects, appending each to the Command object's Parameters collection:

With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "HumanResources.uspUpdateEmployeePersonalInfo"
    For i = 1 To colParams.Count
        .Parameters.Append colParams(i)
    Next i

End With

cmd.Execute

We end by calling the Command.Execute method to send the updated data to the database. Before we run this command, let's take a look at the entire procedure. It should look like this:

Sub UpdateEmpPersonalInfo()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim colParams As Collection
Dim sConnString As String
Dim i As Integer

    Sheets("Sheet1").Activate 'make sure we're on the data sheet

    Set cnn = New ADODB.Connection
    sConnString = "Provider=SQLNCLI;Server=MYSERVERNAMESQLEXPRESS;"
                    & "Database=AdventureWorks;Trusted_Connection=yes;"

    cnn.Open sConnString

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn

    Set colParams = SetParams(ActiveCell.Row)

    With cmd
        .CommandType = adCmdStoredProc
        .CommandText = "HumanResources.uspUpdateEmployeePersonalInfo"
        For i = 1 To colParams.Count
            .Parameters.Append colParams(i)
        Next i

    End With

    cmd.Execute
    cnn.Close
    Set colParams = Nothing
    Set cmd = Nothing
    Set cnn = Nothing

    MsgBox "Record has been updated", vbOKOnly, "Record Processed"

End Sub

Now we'll modify some data and run the procedure. Figure 2-29 shows the data before we make any changes.

Figure 2-29. Employee data before update

Kevin Brown, EmployeeID 2, has been recently married. Change his marital status to M, and then move the cursor to save the change. Run the UpdateEmpPersonalInfo routine, making sure the cursor is in the row containing Kevin's record. The "Record has been updated" message will appear.

To test your success, select and delete all the data from Sheet1 (or just change Kevin's marital status to any character), and run the GetEmpList subroutine again. Your display should look like Figure 2-30.

Figure 2-30. Employee data after update

Of Excel, Data, and Object Orientation

Earlier in this book, I promised that we'd see object-oriented solutions to our coding problems in Excel 2007. Let's take our manager list–creation code and the code that lists a manager's staff, and convert them to classes. Normally, this is the way I would directly approach a solution, but up to this point we've been exploring some of the VBA possibilities in Excel 2007.

Open DataAccessSample04.xlsm and save it as DataAccessSample06.xlsm.

Open Module1 in the VBE and review the GetManagerList subroutine. We can break its functionality down to just a few items. The problem with that is it's doing a number of unrelated things. It's setting up the worksheet for data import, opening a connection to the database, getting data, putting it on the worksheet, and then formatting and cleaning up the worksheet.

When we build our objects, we will pay strict attention to the separation of functionality. The rule of thumb is that objects should do only one well-defined job. Of course there are exceptions, but if you plan carefully, you will develop objects that provide a clearly defined set of methods and properties, providing a focused set of functionality.

The first thing we're doing in our original code is setting up the worksheet by activating it and then clearing a contiguous region in preparation for importing our data:

Set xlSheet = Sheets("Sheet2")
xlSheet.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select

Then we're instantiating and opening a connection to our data:

Set cnn = New ADODB.Connection
sConnString = "Provider=SQLNCLI;Server=MYSERVERNAMESQLEXPRESS;"
                & "Database=AdventureWorks;Trusted_Connection=yes;"

cnn.Open sConnString

Next, we get our data into an ADO recordset and place it on our worksheet:

sSQL = "SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName,"
        & " Person.Contact.LastName FROM Person.Contact"
        & " INNER JOIN HumanResources.Employee"
        & " ON Person.Contact.ContactID = HumanResources.Employee.ContactID"
        & " WHERE (((HumanResources.Employee.EmployeeID) In"
        & " (SELECT  HumanResources.Employee.ManagerID"
        & " FROM HumanResources.Employee)));"

Set rs = New ADODB.Recordset

rs.Open sSQL, cnn, adOpenDynamic

Sheets("Sheet2").Activate
Range("A1").CopyFromRecordset rs

And finally, we do a quick bit of formatting the sheet by using the AutoFit command to resize the data columns:

xlSheet.Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select

These are four simple units of functionality that we can provide in a very generic and reusable object-oriented solution.

In the VBE, add a new class module and name it cData. Add a second new class module and name it cExcelSetup. These will contain the code that will provide all of the functionality provided in our standard code module.

Let's work with cExcelSetup first, and create an object that can provide our worksheet setup and cleanup functionality.

Add three module-level variables:

Private m_xlSheet As Worksheet
Private m_rngInitialCellSelect As Range
Private m_rngDataRegionStart As Range

These are the private variables that will hold key property values for us. Next we'll create read/write properties to set and retrieve our property values:

Public Property Get Worksheet() As Worksheet
    Set Worksheet = m_xlSheet
End Property

Public Property Set Worksheet(newSheet As Worksheet)
    Set m_xlSheet = newSheet
End Property

Public Property Get InitialCellSelection() As Range
    Set InitialCellSelection = m_rngInitialCellSelect
End Property

Public Property Set InitialCellSelection(newCell As Range)
    Set m_rngInitialCellSelect = newCell
End Property

Public Property Get DataRegionStart() As Range
    Set DataRegionStart = m_rngDataRegionStart
End Property

Public Property Set DataRegionStart(newCellAddress As Range)
    Set m_rngDataRegionStart = newCellAddress
End Property

The GetInitialCellSelection and DataRegionStart properties both return Range objects. We'll be using the GetInitialCellSelection property to determine where our cursor will be after our code runs. The DataRegionStart property sets and returns the cell that begins our data region. This is used when we clear the sheet at the start of our procedures and when we perform our autofit during cleanup.

Even though we've got Property Get and Set functions for these two properties, we're going to create an initialization function that allows us to set them both at once. This give us the advantage of using less client code to accomplish the task of setting two properties, yet gives us the flexibility of using the property settings directly if we need to.

Public Sub SetKeyCells(InitialCell As Range, DataRegionStart As Range)
    Set m_rngInitialCellSelect = InitialCell
    Set m_rngDataRegionStart = DataRegionStart
End Sub

Now that we've got our key properties laid out, we can concentrate on adding our setup and cleanup code.

Add a new subroutine called SetupWorksheet, and add the following code:

Public Sub SetupWorksheet()
    Me.Worksheet.Activate
    ClearRegion
    Me.InitialCellSelection.Select
End Sub

This code corresponds to our original code from our standard module:

Set xlSheet = Sheets("Sheet2")
xlSheet.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select

The first and last lines of the SetupWorksheet routine correspond to the first and last lines of our original code. There is a call to a private method called ClearRegion that does the work of the remaining original code:

Private Sub ClearRegion()
    m_xlSheet.Activate
    Me.DataRegionStart.Activate
    Selection.CurrentRegion.Select
    Selection.ClearContents
End Sub

Add one last function to do our autofit cell formatting, and clean up the worksheet:

Public Sub DoAutoFit()
    Me.Worksheet.Select
    Me.DataRegionStart.Select
    Selection.CurrentRegion.Select
    Selection.Columns.AutoFit
    Me.InitialCellSelection.Select
End Sub

By now, I'm sure you've noticed that this code is very similar to the original code in our standard module. The major difference is that rather than referring to specific cells, we are using internal class properties such as Me.DataRegionStart.Select.

That's all there is to our cExcelSetup class. Let's create our cData class to populate our worksheet with data. Add the following private module-level variables:

Private m_cnn As ADODB.Connection
Private m_rs As ADODB.Recordset
Private m_sConnString As String
Private m_sSQL As String

These are the same tools we've been using all along to connect to our data and return sets of data from the AdventureWorks database. We're going to create properties to hold our connection string and SQL statement. We'll also create methods (functions) to open and close our ADO connections and recordsets.

Add the following Property Get/Lets:

Public Property Get ConnectString() As String
    ConnectString = m_sConnString
End Property

Public Property Let ConnectString(newString As String)
    m_sConnString = newString
End Property

Public Property Get SQL() As String
    SQL = m_sSQL
End Property

Public Property Let SQL(newSQL As String)
    m_sSQL = newSQL
End Property

Next we are going to add methods to open and close our ADO Connection object:

Function OpenConnection()
    If m_sConnString <> "" Then
        m_cnn.Open m_sConnString
    Else
        MsgBox "Cannot open connection", vbOKOnly, "cData: OpenConnection Error"
    End If
End Function

Function CloseConnection()
    m_cnn.Close
End Function

Note that the OpenConnection method is using the private variable m_sConnString to return the connection string to the AdventureWorks database.

Next we'll create a new function called GetData and add the following code:

Function GetData() As ADODB.Recordset
    m_rs.Open m_sSQL, m_cnn, adOpenDynamic

    Set GetData = m_rs
End Function

This function returns a dataset based on an SQL statement passed in from the private variable m_sSQL, and uses the private connection object to connect to the database. In reality, this is a very simplistic method. In the real world, we would probably add arguments or properties for the cursor type, location, and other key settings, but for our example this will suffice.

Our last order of business for this class is setting its initialization and termination methods. It is good practice to initialize any internal objects and data variables, and the Class_Initialize method is the place to do it. When using internal objects like the ADO objects, using the Class_Terminate method allows us a place to clean them up when the object goes out of scope in our client code.

Private Sub Class_Initialize()
    m_sConnString = ""
    m_sSQL = ""
    Set m_cnn = New ADODB.Connection
    Set m_rs = New ADODB.Recordset
    Set m_prm = New ADODB.Parameter
    Set m_cmd = New ADODB.Command
End Sub

Private Sub Class_Terminate()
    Set m_cnn = Nothing
    Set m_rs = Nothing
    Set m_prm = Nothing
    Set m_cmd = Nothing
End Sub

Let's take a look at both classes in their entirety (shown in Listings 2-7 and 2-8). Then we'll create client code to use these objects and compare them to the original code in Module1.

Using the cExcelSetup and cData Objects

Now that we've created the objects we need, let's put them to use in client code.

In the VBE, add a new standard module and name it basManagers. Add two module-level variables to hold our cExcelSetup and cData objects:

Dim m_cData As cData
Dim m_cXL As cExcelSetup

These are placed at module level in case we need to use the objects across function calls.

Create a new subroutine and name it GetManagers. Add the following code:

Dim sConnString As String
Dim sSQL  As String
    Set m_cXL = New cExcelSetup
    Set m_cData = New cData
    sConnString = "Provider=SQLNCLI;Server=MyServerNameSQLEXPRESS;"
                    & "Database=AdventureWorks;Trusted_Connection=yes;"
    sSQL = "SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName,"
            & " Person.Contact.LastName FROM Person.Contact"
            & " INNER JOIN HumanResources.Employee"
            & " ON Person.Contact.ContactID = HumanResources.Employee.ContactID"
            & " WHERE (((HumanResources.Employee.EmployeeID) In"
            & " (SELECT  HumanResources.Employee.ManagerID"
            & " FROM HumanResources.Employee)));"

Here we are instantiating our cExcelSetup and cData objects, and preparing variables to set up the cData class.

When we analyzed the original code, we found we needed to have three sets of functionality, prepare the worksheet for data import, get and display the data, and resize the columns for the data. We are going to create helper functions to do most of this work.

Add a new subroutine to basManagers and name it DoClearSheet(). Add the following code:

With m_cXL
    Set .Worksheet = Sheets("Sheet1")
    .SetKeyCells .Worksheet.Range("A1"), .Worksheet.Range("A3")
    .SetupWorksheet
End With

Note that we're using our cExcelSetup object's SetKeyCells method, allowing us to assign values to the InitialCellSelection and DataRegionStart properties with one line of code.

Add another subroutine called GetData. This procedure will take two arguments: the connection string and the SQL statement. Here is the code for the GetData method:

Sub GetData(ConnString As String, which As String)
    With m_cData
        .ConnectString = ConnString
        .OpenConnection
        .SQL = which
        m_cXL.Worksheet.Range("A1").CopyFromRecordset .GetData
        .CloseConnection
    End With
End Sub

Both of these methods use only our cExcelSetup and cData objects with no external code.

Let's finish our GetManagers procedure by adding calls to these methods, and also adding some cleanup code. The entire GetManagers subroutine should look like this:

Sub GetManagers()
Dim sConnString As String
Dim sSQL  As String
    Set m_cXL = New cExcelSetup
    Set m_cData = New cData
    sConnString = "Provider=SQLNCLI;Server=MyServerNameSQLEXPRESS;"
                    & "Database=AdventureWorks;Trusted_Connection=yes;"
    sSQL = "SELECT HumanResources.Employee.EmployeeID, Person.Contact.FirstName,"
            & " Person.Contact.LastName FROM Person.Contact"
            & " INNER JOIN HumanResources.Employee"
            & " ON Person.Contact.ContactID = HumanResources.Employee.ContactID"
            & " WHERE (((HumanResources.Employee.EmployeeID) In"
            & " (SELECT  HumanResources.Employee.ManagerID"
            & " FROM HumanResources.Employee)));"
    DoClearSheet
    GetData sConnString, sSQL
    m_cXL.DoAutoFit
    Set m_cData = Nothing
    Set m_cXL = Nothing
End Sub

I mentioned a moment ago that neither of our helper methods made any direct VBA calls. The same is true of the GetManagers method. All of our work is being done by our objects from start to finish. The beauty of this is that we can drop these classes in any Excel project and have this functionality available instantly.

In our current Excel project, we can change the database and/or SQL statement and import any data we need via the cData object.

Summary

In this chapter, you've taken a look at some of the many data access methods you can use in Excel. You've seen how to use DAO to get data into an Excel workbook. DAO, while old technology, is certainly still a viable alternative for Windows 2000 or XP users. It's easy to use and very fast if you're working with local data sources like an Access database stored on your hard drive. But what if you need to work with remote data? Or what if you need to work with data in a disconnected fashion? In the next section, we began using ActiveX Data Objects (ADO), a technology that addresses these issues and more.

You've also explored various methods of importing data into your Excel workbooks. You've pulled data from Access databases to text files to ODBC and OLE DB data sources. You've also taken a look at how to think of functionality from an object-oriented point of view. Taking existing code that you reuse often or rewrite in a similar manner is a great way to start moving into OOP practices.

As you move into the next chapter and begin looking at some of the XML features of Excel 2007, you'll continue developing objects to do your work for you. Some will provide a level of reusability; some may be one-offs. There is no rule that says all of your code must be reusable. In fact, you might find that you write a lot of code for an application that is specific to that application. This is perfectly acceptable. Reusability is not the only advantage to programming custom objects. Ease of maintenance is another by-product of OOP, and is just as valuable as code reuse.