VB code to use VBA

["Walter Lake" <wlake@xxxxxxxxx>]

One of my trader friends who thinks that Excel is a stunted version of VB
sent this to me. I don't know where it came from.

If you want Excel to be visible set the Visible property to True ... set it
to False if you want it to run in the background.

Best regards

Walter

==================================


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
            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