HOWTO:在 Visual Basic 中自动运行 Microsoft Excel

文章翻译 文章翻译
文章编号: 219151 - 查看本文应用于的产品
展开全部 | 关闭全部

本文内容

概要

本文演示如何通过使用 Visual Basic 的自动化功能创建和操纵 Excel。

更多信息

有两种方法可以控制自动化服务器:通过使用晚期绑定或早期绑定。若使用晚期绑定,则在声明运行时和自动化服务器为“对象”之前不绑定方法。若使用早期绑定,则您的应用程序在设计阶段知道要与之通信的对象的确切类型,并可以作为一个特定类型声明其对象。本示例使用早期绑定,早期绑定被认为是在多数情况下都很适用的方法,因为它可以提供更高的性能和更好的类型安全性。

要早期绑定到自动化服务器,您需要设置对该服务器类型库的引用。在 Visual Basic 中,该操作是通过“项目”|“引用”菜单下的“引用”对话框完成的。对于本示例,您需要向 Excel 的类型库中添加一个引用,然后才能运行代码。关于如何添加引用,请参见下面的步骤。

生成自动化示例

  1. 启动 Visual Basic,新建一个标准 EXE 项目。默认情况下会创建 Form1。
  2. 选择“项目”|“引用”以显示“引用”对话框。向下滚动列表,直到找到“Microsoft Excel 10.0 对象库”,然后选择此项目以向 Excel 2002 中添加一个引用。如果列表中未显示该项目,请检查是否已正确安装了 Excel 2002。

    注意:如果正在自动运行 Microsoft Office Excel 2003,则该类型库在“引用”列表中显示为“Microsoft Excel 11.0 对象库”。如果正在自动运行 Microsoft Excel 2000,则该类型库在“引用”列表中显示为“Microsoft Excel 9.0 对象库”,如果您使用的是 Microsoft Excel 97,则该类型库显示为“Microsoft Excel 8.0 对象库”。
  3. 单击“确定”,关闭“引用”对话框。
  4. 将 CommandButton 添加到 Form1 中。
  5. 在 Form1 的代码窗口中,插入下面的代码:
       Option Explicit
       
       Private Sub Command1_Click()
          Dim oXL As Excel.Application
          Dim oWB As Excel.Workbook
          Dim oSheet As Excel.Worksheet
          Dim oRng As Excel.Range
          
    
          'On Error GoTo Err_Handler
          
       ' Start Excel and get Application object.
          Set oXL = CreateObject("Excel.Application")
          oXL.Visible = True
          
       ' Get a new workbook.
          Set oWB = oXL.Workbooks.Add
          Set oSheet = oWB.ActiveSheet
          
       ' Add table headers going cell by cell.
          oSheet.Cells(1, 1).Value = "First Name"
          oSheet.Cells(1, 2).Value = "Last Name"
          oSheet.Cells(1, 3).Value = "Full Name"
          oSheet.Cells(1, 4).Value = "Salary"
          
    
       ' Format A1:D1 as bold, vertical alignment = center.
          With oSheet.Range("A1", "D1")
             .Font.Bold = True
             .VerticalAlignment = xlVAlignCenter
          End With
          
       ' Create an array to set multiple values at once.
          Dim saNames(5, 2) As String
          saNames(0, 0) = "John"
          saNames(0, 1) = "Smith"
          saNames(1, 0) = "Tom"
          saNames(1, 1) = "Brown"
          saNames(2, 0) = "Sue"
          saNames(2, 1) = "Thomas"
          saNames(3, 0) = "Jane"
    
          saNames(3, 1) = "Jones"
          saNames(4, 0) = "Adam"
          saNames(4, 1) = "Johnson"
          
        ' Fill A2:B6 with an array of values (First and Last Names).
          oSheet.Range("A2", "B6").Value = saNames
          
        ' Fill C2:C6 with a relative formula (=A2 & " " & B2).
          Set oRng = oSheet.Range("C2", "C6")
          oRng.Formula = "=A2 & "" "" & B2"
          
        ' Fill D2:D6 with a formula(=RAND()*100000) and apply format.
          Set oRng = oSheet.Range("D2", "D6")
          oRng.Formula = "=RAND()*100000"
          oRng.NumberFormat = "$0.00"
          
        ' AutoFit columns A:D.
          Set oRng = oSheet.Range("A1", "D1")
          oRng.EntireColumn.AutoFit
          
        ' Manipulate a variable number of columns for Quarterly Sales Data.
          Call DisplayQuarterlySales(oSheet)
          
        ' Make sure Excel is visible and give the user control
        ' of Microsoft Excel's lifetime.
          oXL.Visible = True
          oXL.UserControl = True
          
        ' Make sure you release object references.
          Set oRng = Nothing
          Set oSheet = Nothing
          Set oWB = Nothing
          Set oXL = Nothing
          
       Exit Sub
       Err_Handler:
          MsgBox Err.Description, vbCritical, "Error: " & Err.Number
       End Sub
       
       Private Sub DisplayQuarterlySales(oWS As Excel.Worksheet)
          Dim oResizeRange As Excel.Range
          Dim oChart As Excel.Chart
          Dim iNumQtrs As Integer
          Dim sMsg As String
          Dim iRet As Integer
          
        ' Determine how many quarters to display data for.
          For iNumQtrs = 4 To 2 Step -1
             sMsg = "Enter sales data for" & Str(iNumQtrs) & " quarter(s)?"
             iRet = MsgBox(sMsg, vbYesNo Or vbQuestion _
                Or vbMsgBoxSetForeground, "Quarterly Sales")
             If iRet = vbYes Then Exit For
          Next iNumQtrs
          
    
          sMsg = "Displaying data for" & Str(iNumQtrs) & " quarter(s)."
          MsgBox sMsg, vbMsgBoxSetForeground, "Quarterly Sales"
          
        ' Starting at E1, fill headers for the number of columns selected.
          Set oResizeRange = oWS.Range("E1", "E1").Resize(ColumnSize:=iNumQtrs)
    
          oResizeRange.Formula = "=""Q"" & COLUMN()-4 & CHAR(10) & ""Sales"""
          
        ' Change the Orientation and WrapText properties for the headers.
          oResizeRange.Orientation = 38
          oResizeRange.WrapText = True
          
        ' Fill the interior color of the headers.
          oResizeRange.Interior.ColorIndex = 36
          
        ' Fill the columns with a formula and apply a number format.
          Set oResizeRange = oWS.Range("E2", "E6").Resize(ColumnSize:=iNumQtrs)
          oResizeRange.Formula = "=RAND()*100"
          oResizeRange.NumberFormat = "$0.00"
          
        ' Apply borders to the Sales data and headers.
          Set oResizeRange = oWS.Range("E1", "E6").Resize(ColumnSize:=iNumQtrs)
          oResizeRange.Borders.Weight = xlThin
          
        ' Add a Totals formula for the sales data and apply a border.
          Set oResizeRange = oWS.Range("E8", "E8").Resize(ColumnSize:=iNumQtrs)
          oResizeRange.Formula = "=SUM(E2:E6)"
          With oResizeRange.Borders(xlEdgeBottom)
             .LineStyle = xlDouble
             .Weight = xlThick
          End With
          
        ' Add a Chart for the selected data
          Set oResizeRange = oWS.Range("E2:E6").Resize(ColumnSize:=iNumQtrs)
          Set oChart = oWS.Parent.Charts.Add
          With oChart
             .ChartWizard oResizeRange, xl3DColumn, , xlColumns
             .SeriesCollection(1).XValues = oWS.Range("A2", "A6")
                For iRet = 1 To iNumQtrs
                   .SeriesCollection(iRet).Name = "=""Q" & Str(iRet) & """"
                Next iRet
             .Location xlLocationAsObject, oWS.Name
          End With
          
        ' Move the chart so as not to cover your data.
          With oWS.Shapes("Chart 1")
             .Top = oWS.Rows(10).Top
             .Left = oWS.Columns(2).Left
    
          End With
          
        ' Free any references.
          Set oChart = Nothing
          Set oResizeRange = Nothing
       
       End Sub
    
    						
  6. 按 F5 键运行该项目。

参考

有关 Office 自动化的更多信息,请访问以下 Microsoft Office Development 支持站点:
http://msdn2.microsoft.com/en-us/library/aa188489(office.10).aspx
(c) Microsoft Corporation 1999,保留所有权利。供稿:Richard R. Taylor, Microsoft Corporation。

属性

文章编号: 219151 - 最后修改: 2007年1月17日 - 修订: 5.1
这篇文章中的信息适用于:
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 标准版
  • Microsoft Excel 2000 标准版
  • Microsoft Excel 97 标准版
  • Microsoft Visual Basic 5.0 学习版
  • Microsoft Visual Basic 6.0 学习版
  • Microsoft Visual Basic 5.0 专业版
  • Microsoft Visual Basic 6.0 专业版
  • Microsoft Visual Basic 5.0 企业版
  • Microsoft Visual Basic 6.0 企业版
  • Microsoft Office XP Developer Edition
  • Microsoft Office 2000 Developer Edition
关键字:?
kbhowto kbautomation KB219151
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。

提供反馈

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com