用 VB.NET 執行 Office 的巨集
首先, 先準備一個 office 檔案, 內容加入巨集如下
1 2 3 4 5 6 7 8 9 10 | 'Display a message box that displays the application name. Public Sub DoKbTest() MsgBox "Hello from " & Application.Name End Sub 'Display a message box with the string passed from the 'Automation client. Public Sub DoKbTestWithParameter( sMsg As String ) MsgBox sMsg End Sub |
註: 一個是直接呼叫, 另一個是加入傳入參數
然後, 建立一個新的 VB.Net 專案 (form)
1. 專案裡加入 COM reference, 找到 Microsoft Word 10.0 Object Library or Microsoft Word 11.0 Object Library,然後按一下 [選取].
2. 重複先前步驟中,存取、 Excel 及 PowerPoint Object Library.
3. 將下列程式碼貼到 Button1_Click 中:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | Select Case ComboBox1.SelectedItem Case "Access" Dim oAccess As Access.ApplicationClass 'Start Access and open the database. oAccess = CreateObject("Access.Application") oAccess.Visible = True oAccess.OpenCurrentDatabase("c:db1.mdb", False) 'Run the macros. oAccess.Run ("DoKbTest") oAccess.Run("DoKbTestWithParameter", "Hello from VB .NET Client") 'Clean-up: Quit Access without saving changes to the database. oAccess.DoCmd().Quit (Access.AcQuitOption.acQuitSaveNone) System.Runtime.InteropServices.Marshal.ReleaseComObject (oAccess) oAccess = Nothing Case "Excel" Dim oExcel As Excel.ApplicationClass Dim oBook As Excel.WorkbookClass Dim oBooks As Excel.Workbooks 'Start Excel and open the workbook. oExcel = CreateObject("Excel.Application") oExcel.Visible = True oBooks = oExcel.Workbooks oBook = oBooks.Open("c:book1.xls") 'Run the macros. oExcel.Run ("DoKbTest") oExcel.Run("DoKbTestWithParameter", "Hello from VB .NET Client") 'Clean-up: Close the workbook and quit Excel. oBook.Close (False) System.Runtime.InteropServices.Marshal.ReleaseComObject (oBook) oBook = Nothing System.Runtime.InteropServices.Marshal.ReleaseComObject (oBooks) oBooks = Nothing oExcel.Quit() System.Runtime.InteropServices.Marshal.ReleaseComObject (oExcel) oExcel = Nothing Case "PowerPoint" Dim oPP As PowerPoint.ApplicationClass Dim oPresSet As PowerPoint.Presentations Dim oPres As PowerPoint.PresentationClass 'Start PowerPoint and open the presentation. oPP = CreateObject("PowerPoint.Application") oPP.Visible = True oPresSet = oPP.Presentations oPres = oPresSet.Open("c:pres1.ppt", , , True) 'Run the macros. oPP.Run ("'pres1.ppt'!DoKbTest") oPP.Run("'pres1.ppt'!DoKbTestWithParameter", "Hello from VB .NET Client") 'Clean-up: Close the presentation and quit PowerPoint. oPres.Close() System.Runtime.InteropServices.Marshal.ReleaseComObject (oPres) oPres = Nothing System.Runtime.InteropServices.Marshal.ReleaseComObject (oPresSet) oPresSet = Nothing oPP.Quit() System.Runtime.InteropServices.Marshal.ReleaseComObject (oPP) oPP = Nothing Case "Word" Dim oWord As Word.ApplicationClass 'Start Word and open the document. oWord = CreateObject("Word.Application") oWord.Visible = True oWord.Documents.Open ("C:Doc1.doc") 'Run the macros. oWord.Run ("DoKbTest") oWord.Run("DoKbTestWithParameter", "Hello from VB .NET Client") 'Quit Word. oWord.Quit() System.Runtime.InteropServices.Marshal.ReleaseComObject (oWord) oWord = Nothing End Select GC.Collect() |
4. 在 form1_load 裡加上以下程式碼
1 2 3 4 | ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList Dim a As String() = {"Access", "Excel", "PowerPoint", "Word"} ComboBox1.Items.AddRange(a) ComboBox1.SelectedIndex = 0 |
5. 把以下程式 import 進去
1 2 3 4 | Imports Access = Microsoft.Office.Interop.Access Imports Excel = Microsoft.Office.Interop.Excel Imports Word = Microsoft.Office.Interop.Word Imports PowerPoint = Microsoft.Office.Interop.PowerPoint |
接下來就可以開始測試了