如何從 Visual Basic 將 Microsoft Excel 自動化

文章翻譯 文章翻譯
文章編號: 219151 - 檢視此文章適用的產品。
本文曾發行於 CHT219151
全部展開 | 全部摺疊

在此頁中

結論

本文將告訴您,如何從 Visual Basic 使用「自動化」建立與操作 Excel。

其他相關資訊

有兩種方法可以控制「自動化」伺服器:使用晚期繫結或早期繫結。使用晚期繫結時,方法要等到執行階段才能繫結,而「自動化」伺服器會被宣告為「物件」。使用早期繫結時,應用程式會在設計階段就準確知道它要溝通的物件型別,並且可以將其物件宣告為特定型別。這個範例使用的是早期繫結,在大部分情況下早期繫結被認為是比較好的方法,因為它可以提供較佳的效能以及較好的型別安全。

如果要早期繫結至「自動化」伺服器,您必須設定指向該伺服器的型別程式庫的參考。在 Visual Basic 中,在 [專案] 功能表上按一下 [參考],即可在出現的 [參考] 對話方塊完成這項工作。在這個範例中,您必須新增一個 Excel 的型別程式庫的參考,才能執行此程式碼。請參閱下列新增參考的步驟。

建立自動化範例

  1. 啟動 Visual Basic 並建立新的標準 EXE 專案。根據預設會建立 Form1。
  2. 按一下 [專案],然後按一下 [參考][參考] 對話方塊隨即出現。向下捲動清單,找出 [Microsoft Excel object library],然後選取要新增參考至 Excel 的項目。如果清單中沒有您的 Excel 版本適用的正確物件程式庫,請確定您的 Excel 已正確安裝。

    注意事項
    • 如果您要自動化 Microsoft Office Excel 2007,型別程式庫在 [參考] 清單中應該是顯示為 [Microsoft Excel 12.0 Object Library]
    • 如果您要自動化 Microsoft Office Excel 2003,型別程式庫在 [參考] 清單中應該是顯示為 [Microsoft Excel 11.0 Object Library]
    • 如果您要自動化 Microsoft Excel 2002,型別程式庫在 [參考] 清單中應該是顯示為 [Microsoft Excel 10.0 Object Library]
    • 如果您要自動化 Microsoft Excel 2000,型別程式庫在 [參考] 清單中應該是顯示為 [Microsoft Excel 9.0 Object Library]
    • 如果您要自動化 Microsoft Excel 97,型別程式庫在 [參考] 清單中應該是顯示為 [Microsoft Excel 8.0 Object Library]
  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://support.microsoft.com/ofd
(c) Microsoft Corporation 1999. All Rights Reserved.本文內容由 Microsoft Corporation,Richard R. Taylor 提供。

屬性

文章編號: 219151 - 上次校閱: 2007年5月18日 - 版次: 5.0
這篇文章中的資訊適用於:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic 5.0 Learning Edition
  • Microsoft Visual Basic 6.0 Learning Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition
  • Microsoft Office XP Developer Edition
  • Microsoft Office 2000 Developer Edition
關鍵字:?
kbexpertisebeginner kbautomation kbhowto 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