These ranged from using Excel??™s import methods to getting DAO and ADO recordsets and
placing their data on your worksheets.
In our ActiveX example, we??™ll take one of these data-driven samples and see how they??™d
happen in a compiled component or DLL. Then we??™ll look at how we can use that compiled
component to add custom functionality to our projects.
I??™m including the VB 6 code, but don??™t worry if you??™re not a VB programmer. The DLLs are
also included with the source files for this book, so you can access the functionality. You just
won??™t have access to the code.
nNote The supporting files and source code for this book are available at www.apress.com, in the
Download section of this book??™s home page.
Using an ActiveX Component in Excel 2007
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.
CHAPTER 9 n ACTIVEX AND .NET 316
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(1) As String
Dim sSQL As String
Dim iFieldCount As Integer
Dim i As Integer
arr_sPath(0) = "C:\projects\Excel2007Book\Files\northwind 2007.accdb"
arr_sPath(1) = "C:\projects\Excel2007Book\Files\northwind.
Pages:
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295