Using an ActiveX Component in Excel

Our ActiveX example allows us to put data on the worksheet of our choice in our current workbook and pass in a SQL statement to retrieve whatever data we need. This example will mimic the functionality of our ADO example from Chapter 2 in the file DataAccessSample03.xlsm. Listing 9-1 shows the original VBA code we wrote in Chapter 2.

Listing 9-1. ADOTest Macro from Chapter 2 Sub ADOTest()

Dim cnn As New ADODB.Connection Dim rs As ADODB.Recordset Dim xlSheet As Worksheet Dim sConnString As String Dim arr_sPath(l) As String Dim sSOL As String Dim iFieldCount As Integer Dim i As Integer arr_sPath(0) = "C:\projects\Excel2007Book\Files\northwind 2007.accdb" arr_sPath(l) = "C:\projects\Excel2007Book\Files\northwind.mdb"

Set xlSheet = Sheets("Sheet1")

xlSheet.Activate

Range("Al").Activate

Selection.CurrentRegion.Select

Selection.ClearContents

Range( "Al").Select

' Open connection to the database cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &

"Data Source=" & arr_sPath(0) & ";" ''When using the Access 2007 Northwind database ''comment the previous code and uncomment the following code. cnn.Open "Provider=Microsoft.ACE.OLEDB.l2.0;" & ^ "Data Source=" & arr_sPath(0) & ";"

Set rs = New ADODB.Recordset ' Open recordset based on Orders table rs.Open "Select * From Orders", cnn iFieldCount = rs.Fields.Count For i = 1 To iFieldCount xlSheet.Cells(l, i).Value = rs.Fields(i - l).Name Next i

' Copy the recordset to the worksheet, starting in cell A2 xlSheet.Cells(2, l).CopyFromRecordset rs xlSheet.Select 'Range( "Al").Select Selection.CurrentRegion.Select Selection.Columns.AutoFit 'Range( "Al").Select rs.Close cnn.Close

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

This code dropped the result of a SQL SELECT statement onto Sheet1 in our sample file. Our ActiveX component allows us to choose which worksheet we put our data on and select which data we want, giving us a quick tool for querying the Northwind database.

The VB 6 code here is also split into a data class and a second class named cExcelNwind. The data class, cData, is doing some work this time. Its GetData method will return a recordset to the cExcelNwind class. The cExcelNwind class will do the work of placing the data on the worksheet that is passed into the ActiveX component. Listings 9-2 and 9-3 show the VB 6 code.

Listing 9-2. cData Class from the ActiveX Component Option Explicit

Const m_sDBPathName As String = "C:\Book\Files\Northwind 2007.accdb" Private m_oCnn As ADODB.Connection Private m oRS As ADODB.Recordset

Public Function GetData(Which As String) As ADODB.Recordset m_oCnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & w "Data Source=" & m_sDBPathName & ";"

Set m_oRS = New ADODB.Recordset m_oRS.Open Which, m_oCnn

Set GetData = m_oRS End Function

Private Sub Class_Initialize()

Set m_oCnn = New ADODB.Connection Set m_oRS = New ADODB.Recordset End Sub

Private Sub Class_Terminate() Set m_oCnn = Nothing Set m_oRS = Nothing End Sub

Listing 9-3. cExcelNwind Class from the ActiveX Component Option Explicit

Public Sub PlaceData(TheWorksheet As Excel.Worksheet, WhichData As String)

Dim oData As cData

Dim xl As Excel.Application

Dim rs As ADODB.Recordset

Dim iFieldCount As Integer

Dim i As Integer

Set xl = TheWorksheet.Application 'hook into the current Excel session

TheWorksheet.Activate

TheWorksheet.Range("A1").Activate xl.Selection.CurrentRegion.Select xl.Selection.ClearContents

TheWorksheet.Range("A1").Select

Set oData = New cData

Set rs = oData.GetData(WhichData)

iFieldCount = rs.Fields.Count For i = 1 To iFieldCount

TheWorksheet.Cells(1, i).Value = rs.Fields(i - 1).Name Next i

TheWorksheet.Cells(2, 1).CopyFromRecordset rs

TheWorksheet.Select xl.Selection.CurrentRegion.Select xl.Selection.Columns.AutoFit rs.Close

Set TheWorksheet = Nothing Set rs = Nothing Set xl = Nothing End Sub

Note Once again, the VB 6 code provided here is for reference only, showing the similarities to code we've already experienced. We will not dig into the specifics of compiling DLLs or ActiveX EXEs here.

As you'll see in this next bit of client code, accessing the data from an external component really helps keep the code on the client application simple. The compiled version of this code is available at www.apress.com in the Download section of this book's home page. It is called Nwind2Excel.dll. You must register the DLL before you can access its functionality.

Registering Nwind2Excel.dll in Windows XP or 2000

1. Copy the file to a folder on your local machine.

3. Type regsvr32.exe, followed by a space, and then the full path to Nwind2Excel.dll. Listing 9-4 shows an example of this command.

Listing 9-4. Example of Run Command to Register a DLL regsvr32.exe C:\MyComponents\Nwind2Excel.dll

4. Click OK to register the DLL.

5. Click OK when the success message appears.

Caution In the VB code, be sure to change the path to the Northwind 2007 database to wherever it can be found on your local machine. If you are using the compiled DLL, it needs to find the Northwind database in C:\ExampleDBs.You must create that folder and put the Northwind 2007 database there.

Registering Nwind2Excel.dll in Windows Vista

1. Open a command prompt window by selecting Start > All Programs > Accessories > Command Prompt.

Note This command prompt must be run using the Run as Administrator right-click menu option.

2. Type regsvr32.exe, followed by a space, and then the full path to Nwind2Excel.dll. Listing 9-4 (shown previously) shows an example of this command.

3. Press Enter to run the command.

4. Click OK when the success message appears. Figure 9-1 shows the success message you should see.

Figure 9-1. regsvr32.exe shows a success message upon registering a DLL in Windows Vista.

Now we can use the DLL in our project.

1. Open a new workbook in Excel.

2. Open the VBE by choosing the Developer ribbon > Visual Basic or by pressing Alt+F11.

3. Add a standard code module in the Project Explorer.

4. Add a reference to the new DLL by selecting Tools > References.

5. In the References dialog box, scroll down until you see an item named !Northwind2Excel Object, as shown in Figure 9-2.

References - VBAProject

Available References:

0 Visual Basic For Applications 0 Microsoft Excel 12.0 Object Library 0 OLE Automation

0 Microsoft Office 12.0 Object Library_

0 ujmamfflH JBMI^^^m

□ IAS Helper COM Component 1.0 Type Library

□ IAS RADIUS Protocol 1.0 Type Library

□ 8021XConfig 1.0 Type Library

□ Acrobat Distiller

□ AcroIEHelper 1.0 Type Library

□ Active DS IIS Extension Dll

□ Active DS IIS Namespace Provider

□ Active DS Type Library

□ Active Setuo Control Library jJ___I

Priority ¿1

!Northwind2Excel Object

Location: C:\Data\VB6Proj\ADODataClass\Nwind2Excel.dll Language: Standard

Help

Figure 9-2. Adding a reference to !Northwind2Excel Object

Tip When you create custom objects, they tend to get lost in the list in the References dialog box. Adding the bang (!) character as a prefix helps keep your custom objects near the top of the list and makes them easier to find.

6. Select it from the list and click OK to close the dialog and store the reference. Now we are ready for a small bit of client code.

The Client Code

The really neat thing about using custom DLLs to provide functionality is that it lets us use such a tiny bit of code in our applications.

On the standard module you just created, add the following code: Option Explicit

Sub GetNorthwindData()

Dim oNwindData As cExcelNwind

Set oNwindData = New cExcelNwind oNwindData.PlaceData ThisWorkbook.Sheets("Sheet1"), "Select * From Orders" oNwindData.PlaceData ThisWorkbook.Sheets("Sheet2"), "Select * From Employees"

Set oNwindData = Nothing End Sub

That's about it. The two lines of code between the Set statements do all the work, with each line placing the result of its SQL statement on the worksheet referenced. Let's run the code.

1. Return to Excel.

2. Save the file.

3. Open the Macros dialog box by selecting the Developer ribbon > Macros command.

4. Choose GetNorthwindData from the List of macros.

5. Click the Run button.

Caution As noted earlier, if you are using the compiled DLL that comes with the source code for this book, you must create the path C:\ExampleDBs and place the Northwind 2007 database there.

The result is shown in Figure 9-3. Sheet1 contains the order information and Sheet2 contains the employee information.

Figure 9-3. Result of GetNorthwindData macro 6. Save the workbook if you like.

Note Here's one of the comparative advantages of the .NET platform vs. ActiveX. In the .NET world, we do not have to deal with registration of components like we do with ActiveX components. We can simply copy our component to any machine that has the correct version of the .NET Framework installed, and it will run.

One benefit that we see from this example is minimal code in our project. Let's take a look now at how .NET technologies can actually take the code out of our Excel projects.

0 0

Post a comment