XL: Automation Example: Controlling MS Excel from VB

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Visual Basic version 3.0, Standard or Professional Editions
• Microsoft Visual Basic version 4.0, Standard or Professional Editions

SUMMARY

With Microsoft Visual Basic and Automation, you can use Microsoft Excel Visual Basic for Applications commands from within a Microsoft Visual Basic 3.0 or 4.0 procedure.

MORE INFORMATION

The following code is an example of how to send commands to Microsoft Excel to create a three-dimensional (3-D) column chart from new information, rotate the chart, and close Microsoft Excel.

Visual Basic Code Example

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Sub Command1_Click ()

    Dim ExcelApp As Object
    Dim ExcelChart As Object
    Dim ChartTypeVal As Integer
    Dim i as Integer

    '-4100 is the value for the MS Excel constant xl3DColumn. Visual
    'Basic does not understand MS Excel constants, so the value must be
    'used instead.
    ChartTypeVal = -4100

    'Creates OLE object to MS Excel
    '  the registered OLE application name for MS Excel version 97 is
    '  excel.application.8
    Set ExcelApp = CreateObject("excel.application")

    'Sending VB Applications Edition commands to MS Excel via the new OLE
    'object to create a new workbook fill in numbers, create the chart, and
    'rotate the chart.
    ExcelApp.Visible = True
    ExcelApp.Workbooks.Add
    ExcelApp.Range("a1").Value = 3
    ExcelApp.Range("a2").Value = 2
    ExcelApp.Range("a3").Value = 1
    ExcelApp.Range("a1:a3").Select
    Set ExcelChart = ExcelApp.Charts.Add()
    ExcelChart.Type = ChartTypeVal
    For i = 30 To 180 Step 10
        ExcelChart.Rotation = i
    Next

    'You must use square brackets around the Close method of the Workbook
    'object because Close is also a reserved word in Visual Basic.
    'Using the square brackets tells Visual Basic that this is an
    'MS Excel command not a Visual Basic command.
    '  in VB4 remove the brackets [ ] around the word close.
    ExcelApp.ActiveWorkbook.[Close](False)
    ExcelApp.Quit
    Set ExcelChart = Nothing
    Set ExcelApp = Nothing
    End
End Sub