To finish with, here's an easy little application that places values from a VB application into an Excel spreadsheet. There are project-level (early bound) references created to both Excel and the ADODB 2.0 Reference Library. An ADO recordset has already been created and is passed as a parameter to the OutputToExcel function. The function creates an instance of a new Excel workbook and worksheet, then copies the values from the ADO recordset into the worksheet. Excel's functionality is used to perform a simple calculation on the data, the worksheet is saved, Excel is closed down, and all references are tidied up.
This example illustrates the power of a glue language such as Visual Basic. Here VB is acting as the glue between ADO, which is an ActiveX server, and Excel— controlling both to produce a simple yet patently powerful and seamless application:
Private Function OutputToExcel(oADORec As ADODB.Recordset) _ As Boolean
On Error GoTo cmdExcel_Err
'set up the default return value OutputToExcel = False
' Declare the Excel object variables Dim oXLApp As Excel.Application Dim oXLWBook As Excel.Workbook Dim oXLWSheet As Excel.Worksheet
'start at the top of the model Set oXLApp = New Excel.Application 'and work your way down Set oXLWBook = oXLApp.Workbooks.Add 'until you get to the worksheet Set oXLWSheet = oXLWBook.Worksheets.Add oXLWSheet.Cells(1, 1).Value = oADORec!FirstValue oXLWSheet.Cells(2, 1).Value = oADORec!SecondValue
' do some stuff in Excel with the values oXLWSheet.Cells(3, 1).Formula = "=R1C1 + R2C1"
' save your work oXLWSheet.SaveAs "vb2XL.xls"
88 Chapter 5 - Automation oXLApp.Quit
' always remember to tidy up before you leave Set oXLWSheet = Nothing Set oXLWBook = Nothing Set oXLApp = Nothing
OutputToExcel = True
Exit Function cmdExcel_Err:
MsgBox Err.Description & vbCrLf & Err.Number & _ vbCrLf & Err.Source
Was this article helpful?