您目前已離線,請等候您的網際網路重新連線

如何從 Visual Basic 將 Microsoft Excel 自動化

Office 2003 支援已結束

Microsoft 於 2014 年 4 月 8 日結束對 Office 2003 的支援。此變更已影響您的軟體更新和安全性選項。 瞭解這對您的意義為何且如何持續受保護。

本文曾發行於 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 支援網站: (c) Microsoft Corporation 1999. All Rights Reserved.本文內容由 Microsoft Corporation,Richard R. Taylor 提供。

XL2007
內容

文章識別碼:219151 - 最後檢閱時間:05/18/2007 11:16:03 - 修訂: 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
意見反應
"; var Ctrl = ""; document.write("