Microsoft KB Archive/124094

{|
 * width="100%"|

INFO: Automation Example: Controlling MS Excel from VB

 * }

Q124094

-

The information in this article applies to:


 * 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 Standard and Professional Editions for Windows, versions 3.0, 4.0

-

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
Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft Support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

"http://support.microsoft.com/directory/overview.asp" 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
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 To use this subroutine, create a form in Visual Basic and add a single button to that form. Double-click the button and type the code shown above in the procedure window. Close the procedure window, and click Start on the Run menu. To see the rotating chart, click on the button in your form.