Output from VB to Excel

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

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"

'quit Excel

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

End Function

Was this article helpful?

0 0

Post a comment