Example: Using Excel

This example uses OLE automation with Microsoft Excel. It creates a simple application that makes a new Excel worksheet, and then add values to the worksheet. The program makes Excel visible while it manipulates the Excel objects via OLE automation.

The source code needed for this example is stored in the "Source Code\Chapter22\Excel Demo" directory.

Private Sub cmdExcel_Click()
   ' Application Object
   Dim obExcel As Object
   ' Worksheet Object
   Dim obWS As Object

   ' if an error occurs move to the next statement
   On Error Resume Next

   ' Set the Excel application reference
   Set obExcel = CreateObject("Excel.Application")

   ' add a new workbook
   obExcel.Workbooks.Add

   ' make Excel visible
   obExcel.Visible = True

   'reference the active sheet
   Set obWS = obExcel.ActiveSheet

   ' enter values in active sheet's cells
   obWS.Cells(1, 1).Value = "Name"
   obWS.Cells(1, 2).Value = "Age"
   obWS.Cells(2, 1).Value = "Sachin Tendulkar"
   obWS.Cells(2, 2).Value = "29"

End Sub

Private Sub Form_Unload(Cancel As Integer)
   'release the object variable
   Set obExcel = Nothing
End Sub

Run the application, you will see the simple worksheet shown in the following figure:

The important thing to remember is that VB used Excel's brain to create a worksheet.