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")
' 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.Range("A1").Activate xl.Selection.CurrentRegion.Select xl.Selection.ClearContents
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.
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
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
Location: C:\Data\VB6Proj\ADODataClass\Nwind2Excel.dll Language: Standard
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
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.
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.
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.
Was this article helpful?