/ Excel, VBA

用 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

接下來就可以開始測試了