ActiveX Data Objects (ADO) can be used to access virtually any type of database via VBScript, as well as from a variety of other programming languages. In this article I’ll focus on how to use ADO to connect to and access data contained within Microsoft Excel spreadsheets using VBScript. (For basic information about ADO, see the Learning Path.) ADO gives you quite a bit of power: You can be very specific regarding what to retrieve via Select statements that define the conditions you want to focus on, so you don’t necessarily need to pull in an entire spreadsheet every time you want to query your data. ADO also provides methods that let you easily find and filter your data. In addition, you can easily modify cell values within a spreadsheet and add new records to a spreadsheet.
ADOExcel.vbs
I wrote the script ADOExcel.vbs, which Listing 1 shows, to demonstrate how you can use ADO to create new Excel records, modify cell values, find existing records, and filter records. The script creates a working spreadsheet in your Temp folder called ~Test~Spread~Sheet.xls (or ~Test~Spread~Sheet.xlsx if you’re using Excel 2007). The spreadsheet also contains a worksheet called ADOExcel.
To use the script in Listing 1, you might need to change the extension of the source Excel file. Excel 2007 uses the .xlsx extension, as shown in callout A, whereas earlier versions of Excel use .xls. Change the extension of the source file to match the version of Excel installed on your machine. If you’re using Excel 2007, you can also access spreadsheets created in earlier versions of Excel; just make sure to use the .xls extension when referencing those files.
The script creates a demonstration spreadsheet that ADO will later access. First, an Excel workbook is created and a worksheet named ADOExcel is added to the workbook. The worksheet, which has a dimension of four columns and three rows, is then populated with test data. The worksheet contains a header row with the heading names Header0, Header1, Header2, and Header3. I start with Header0 because ADO field references start with zero rather than one. Each cell within the worksheet contains a value that refers to its row and column numbers. For instance, the cell at row 2 column A contains the value RecordField 2@0, where Record refers to the row number and Field refers to the header column number. Note that column A in this case refers to Header0.
I created the spreadsheet with this layout to illustrate the similarities between a database and a spreadsheet. The workbook itself is the database, the worksheet (i.e., ADOExcel) is the table, and the columns (Header0 through Header3) and rows are fields and records.
Setting Up ADO
In order to set up ADO, the first thing you must do is set up an ADO Connection object. This will create a connection between the script and the database (i.e., spreadsheet). You can accomplish this step with the code
Set oConn = CreateObject("ADODB.Connection")
Next, you need to set up a connection string. The connection string contains such information as the ODBC provider, the source filename and path, and whether or not the database has headers. Several ODBC providers are available, but you’ll only be concerned with Provider=Microsoft.ACE.OLEDB.12.0 (for Excel 2007 spreadsheets) and Provider=Microsoft.Jet.OLEDB.4.0 (for pre-Excel 2007 spreadsheets). Note that you can use the Microsoft.ACE.OLEDB.12.0 provider to access pre-Excel 2007 spreadsheets, although you won’t see this provider as available unless you have Excel 2007 installed.
As you can see in callout B, the connection strings for both providers are included so that you can easily select the appropriate provider. I also included the connection string if you use a Universal Data Link (i.e., a file that contains all the connection information). By default, the script uses Excel 2007's Microsoft.ACE.OLEDB.12.0 provider connection string. If you need to use one of the other connection strings, uncomment that code and comment out the Microsoft.ACE.OLEDB.12.0 provider connection string. Make sure you have only one connection string uncommented when you run the script.
Let's take a closer look at the connection string for Excel 2007. You can tell by the provider that I’m attempting to access a Microsoft Office 2007 Access database or an Excel 2007 spreadsheet. The Data Source element of the connection string defines the spreadsheet’s full path and filename. In this case I chose to store the path and filename in a variable named SourceXLS, as referenced in callout A. The Extended Properties element of the connection string specifies that you’ll be connecting to an Excel spreadsheet that has headers.
With the Connection object and connection string in place, all that’s left to do to open the connection is to call the Connection Open method, using the code oConn.Open. Even though you have an open connection, you must still create a Recordset object to be able to work with the data in the spreadsheet. A Recordset object can be comprised of all or just part of a database. Callout C shows the code to create the Recordset object.
The first two lines at callout C simply set up two constant names and values. These lines help clarify the code in the fourth line, rather than using just numbers in the statement. The third line of code at callout C actually creates the Recordset object. The fourth line of code uses the Recordset object's Open method to open the recordset. The Open method follows the syntax
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Source is the SQL statement Select * from [ADOExcel$]. ActiveConnection is the active ADO Connection object oConn. The CursorType is adOpenKeyset, which refers to the type of cursor the provider should use when opening the recordset. Note that the term cursor is an acronym that stands for CURrent Set Of Records. An adOpenKeyset CursorType indicates that additions, changes, and deletions by other users are visible, and all types of movement through the recordset are allowed except that you can't see records that other users add. The LockType determines what type of locking the provider uses when opening the recordset. A LockType of adLockOptimistic indicates that the provider uses optimistic locking, which locks records only when you call the Update method. Listing 1 doesn’t contain the Options parameter, so I don't discuss it here.
As you can see, this Open method does a lot of work at once. It uses the ADO connection to specify what to return as a recordset, with an easy-to-use Select statement. It also defines how you can move around within the recordset and what type of locking methods to use when you perform an update.
You can structure your Select statement to access whichever records you want. You can easily add a Where clause to return only the records that conform to your conditions, as the following code shows:
oRS.Open "SELECT * FROM [ADOExcel$] WHERE Header0 LIKE '*3*'", _
oConn, adOpenKeyset,adLockOptimistic
This code gives you a recordset of your database that contains only records with the number 3 in the Header0 field. Note that the ADOExcel worksheet must be enclosed within braces and must end with a dollar sign in this statement.
Accessing Records
After you’re connected to the data source and have a working recordset, you can add records, modify records, and update the spreadsheet (i.e., database). Note that the spreadsheet is open while these updates and changes are taking place, so you can see the changes as they happen. In addition, the code in Listing 1 includes message boxes to indicate what is taking place and to show before and after values of modified fields.
First, the code displays some basic but important information about the connection and the recordset: the ADO version, the connection provider, the number of records in the recordset, and the number of fields in the recordset, as the code at callout D shows. To get the ADO version that you’re current using, simply reference the Version property of the Connection object. To find the connection provider, reference the Connection object Provider property. To obtain the record count, reference the Recordset object’s RecordCount property. Finally, to determine the number of fields in the recordset, reference the Recordset object’s Fields.Count property.
Now, you can make changes to the spreadsheet. The code at callout E shows how to add new records. The For Next loop, which I call my Record loop, adds two records. The first action within this loop uses the MoveLast method of the Recordset object to move to the last record in the recordset. Although this step isn’t necessary when adding new records, I use it to obtain the record number of the last record so I can add one to that value, then store that number to a variable called NewPosition for later use as a value in my new record. To add a new record, you simply call the AddNew method of the Recordset object.
Once you have a new record, you need to populate all its fields. Another For Next loop within the Record loop adds a value to each of the fields. To obtain the current record number, use the AbsolutePosition property of the Recordset object. After the script adds values to the fields, it calls the Update method of the Recordset object to perform the actual writing of data to the database (i.e., spreadsheet). After the two records are added, a MsgBox lets the user know what has happened and what will occur next.
The code within callout F changes the values of three fields for the first 2 records. I want to change the fields Header1, Header2, and Header3 within each record. Calling the MoveFirst method of the Recordset object moves to the first record. Then, a simple For Next loop changes Header1's and Header2's values to the string value ChangedRec and Header3's value to FindAndFilterThese by setting the Value property of the field items. As you can see, assigning a value to a field is relatively easy. To make sure the new values are written to the spreadsheet, you follow up by calling the Update method as before. In my example, you simply move to the next record and repeat the process to modify the first two records.
The code at callout G moves to the next record and demonstrates how to store the value of a field to a variable. This process is similar to changing the field values; however, the value of the assignment variable in the Fields.Item value statement is stored, simply to show the before and after values of a field that’s about to change.
Next, you can use the Recordset object's Find method to find a record that has a certain value. The code within callout H accomplishes this operation. You’re looking for the fields with the values ChangedRec, which you set up previously. Be sure to move to the first record before calling your first instance of the Find method; otherwise, the search will start with the record you’re currently on. The Find call syntax is
oRS.Find("Header1 LIKE '*ChangedRec*'")
You specify your Find criteria by indicating a condition that must be met. The code Header1 LIKE '*ChangedRec*' means that you want to find the next record in the recordset for which the contents of the value stored in the Header1 field contains the string ChangedRec. The LIKE operator provides a good deal of flexibility because you can use wildcards with it. The asterisks in this code are wildcards. You can use other operators to define your condition, such as the equal sign, the greater than sign, the less than sign, and the not equal sign. However, you can’t use wildcards when you use these operators. Also note the use of the double and single quotes within the condition.
You might have more than one record that meets your Find condition. After you find the first record, you need to call the Find method again until you reach an end of file (EOF) marker. The statement
If Not oRS.EOF Then
accomplishes this task. This section of the code simply finds the records that have a field containing a certain value, then modifies a field within that record.
The last section of the script sets a filter on the recordset. The Filter property limits the records that are accessible while the filter is in place. Setting a filter is a simple task, as you can see in the code at callout I. The statement
oRS.Filter = "Header3 LIKE '*FindAndFilterThese*'"
filters the existing recordset so that only records with Header3 values that contain the string FindAndFilterThese are accessible. While the filter is in place, you can use the MoveNext method to cycle through all the records in the recordset that meet this filter condition. Filter conditions are much like Find conditions in structure, although their syntax is different. Keep in mind that you must release the filter if you want to return to a full recordset. To release the filter, set the filter property equal to an empty string (""), like so:
oRS.Filter = ""
The last segments of the script simply show that the record count differs from the actual database record count when a filter is in place. Finally, the script uses the Close method of the Connection object to close down the connection.
To Be Continued...
The script ADOExcel.vbs demonstrates how to use ADO to access Excel. Next month, I’ll expand on this article to provide more information about using ADO with Excel spreadsheets.
End of Article
Register
