Visual Basic 2017 操作Excel和word【1】持续更新……

我坚持在VB的路上走到黑…………

  清单1.1  从应用程序对象导航到Excel中的工作表  

Dim myWorkbooks As Excel.Workbooks = app.Workbooks
Dim myWorkbook As Excel.Workbook = myWorkbooks.Item(1)
Dim myWorksheets As Excel.Sheets = myWorkbook.Worksheets
Dim myWorksheet As Excel.Worksheet
myWorksheet = CType(myWorksheets.Item(1), Excel.Worksheet）

如果代码不需要在变量中缓存每个对象模型对象，但只需要获取一个Worksheet对象，则编写此代码的更有效的方法如下所示：

Dim myWorksheet As Excel.Worksheet
myWorksheet = CType(app.Workbooks.Item(1).Worksheets.Item(1), Excel.Worksheet)

活代码：第一步：创建visual basic 2017的窗体应用程序（运行环境：win 10+visual studio 2017+office 2010）

              第二步：添加引用：COM的 "Microsoft  Office  14.0 Object  Library  2.5"(指的是Office 2010)和“程序集”的"扩展"中的"Microsoft.office.Interop.Excel 14.0.0.0"

              第三步：代码

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim app As Excel.Application = New Excel.Application
        Dim myWorkbook As Excel.Workbook
        Dim myWorksheet As Excel.Worksheet
        app.Visible = True
        myWorkbook = app.Workbooks.Add()
        myWorksheet = CType(myWorkbook.Sheets.Add(), Excel.Worksheet）
        myWorksheet.Cells(1, 1) = "这是A1"
    End Sub
End Class

              第四步：运行结果

       

                          

清单1.2  使用整数或String作为Count属性和Item属性索引遍历工作表集合

Dim myWorkbooks As Excel.Workbooks = app.Workbooks

Dim workbookCount As Integer = myWorkbooks.Count
For i As Integer = 1 To workbookCount
  ' Get the workbook by its integer index
  Dim myWorkbook As Excel.Workbook = myWorkbooks.Item(i)

  ' Get the workbook by its string index
  Dim workbookName As String = myWorkbook.Name

  Dim myWorkbook2 As Excel.Workbook = myWorkbooks.Item(workbookName)
  MsgBox(String.Format("Workbook {0}", myWorkbook2.Name))
Next

活代码：

活代码：第一步：创建visual basic 2017的窗体应用程序（运行环境：win 10+visual studio 2017+office 2010）

              第二步：添加引用：COM的 "Microsoft  Office  14.0 Object  Library  2.5"(指的是Office 2010)和“程序集”的"扩展"中的"Microsoft.office.Interop.Excel 14.0.0.0"

              第三步：代码

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim app As Excel.Application = New Excel.Application
        Dim myWorkbook As Excel.Workbook
        Dim myWorksheet As Excel.Worksheet
        app.Visible = True
        myWorkbook = app.Workbooks.Add()
        myWorksheet = CType(myWorkbook.Sheets.Add(), Excel.Worksheet）
        myWorksheet.Cells(1, 1) = "这是A1"

        Dim worksheetCount As Integer = myWorkbook.Worksheets.Count
        For i As Integer = 1 To worksheetCount
            Dim str As String
            str = myWorkbook.Worksheets.Item(i).Name  '以整数作为索引
            MessageBox.Show(str, "获取工作表名称")
        Next

        For i As Integer = 1 To worksheetCount
            Dim str As String
            str = myWorkbook.Worksheets.Item("sheet" & i).Name
            MessageBox.Show(str, "获取工作表名称")    '以字符串作为索引
        Next

    End Sub
End Class

            第四步：运行结果

                     

拓展假如要编辑工作表sheet2中D3单元格，并填入“我是丑丑”，该如何实现呢

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim app As Excel.Application = New Excel.Application
        Dim myWorkbook As Excel.Workbook
        Dim myWorksheet As Excel.Worksheet
        app.Visible = True
        myWorkbook = app.Workbooks.Add()
        'myWorksheet = CType(myWorkbook.Sheets.Add(, , 3,), Excel.Worksheet） '第3个参数表示添加多少工作表，这里添加3个，
        Dim C4_sheet As Excel.Worksheet = myWorkbook.Worksheets.Item("sheet2") '工作表的索引从0开始,
        C4_sheet.Cells(3, 4) = "我是丑丑" 'Cells(行，列)，也就是D3单元格
    End Sub
End Class

运行结果：

 

清单1.3 使用For  Each结构遍历工作簿集合

Dim myWorkbooks As Excel.Workbooks = app.Workbooks

For Each workbook As Excel.Workbook In myWorkbooks
  MsgBox(String.Format("Workbook {0}", workbook.Name))
Next

 活代码：略

清单1.4  当删除对象时使用辅助集合

 

Dim myWorkbook As Excel.Workbook = app.ActiveWorkbook
Dim myCollection As New Collections.Generic.List(Of Excel.Name)

For Each name As Excel.Name In myWorkbook.Names
  myCollection.Add(name)
Next

For Each name As Excel.Name In myCollection
  name.Delete()
Next

 

活代码：第一步：创建visual basic 2017的窗体应用程序（运行环境：win 10+visual studio 2017+office 2010）

              第二步：添加引用：COM的 "Microsoft  Office  14.0 Object  Library  2.5"(指的是Office 2010)和“程序集”的"扩展"中的"Microsoft.office.Interop.Excel 14.0.0.0"

              第三步：代码

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim app As Excel.Application = New Excel.Application
        Dim myWorkbook As Excel.Workbook = app.ActiveWorkbook
        app.Visible = True
        myWorkbook = app.Workbooks.Add("E:\工作簿1")
        Dim myCollection As New Collections.Generic.List(Of Excel.Range)
        For Each Name As Excel.Range In myWorkbook.Worksheets("Sheet1").Range("A1:D5")  '由于是删除，所以下面的行会自动向上缩进
            myCollection.Add(Name)
        Next
        For Each name As Excel.Range In myCollection
            name.Delete()   '由于是删除，所以下面的行会自动向上缩进
        Next
    End Sub
End Class

运行结果：

原图：                                             

      

表1.2  从Word的应用程序对象中选择的属性，方法和事件

名称	作用
属性
ActiveDocument	返回一个用户正在编辑的word文档对象
ActivePrinter	获取并设置默认打印机
Caption	为word应用程序设置标题，默认被设置为 "Microsoft Word"
Documents	返回一个打开的word文档集
方法
Activate	将word显示在最前，并且作为活动窗口
NewWindow	创建一个新的Word窗口来显示活动窗口，并返回一个新的窗口模型对象 。
Quit	关闭word程序
事件
DocumentBeforeClose	这是一个在文档关闭之前发生的事件。如果代码句柄将参数 Cancel 设置为true, 文档将不会关闭
DocumentOpen	打开文档时引发的事件。 要打开的文档的Document对象作为参数传递给事件。
WindowActivate	当用户激活Word窗口时，通常通过单击非活动窗口，从而使其处于活动状态，引发事件。 正在激活的文档的Document对象作为参数传递给事件以及激活的窗口的Window对象（因为两个窗口可能显示相同的文档）。

 清单1.5  返回值类型的属性：Word应用程序对象上的布尔CapsLock属性

If app.CapsLock Then
  MsgBox("CapsLock is on")
Else
  MsgBox("CapsLock is off")
End If

清单1.6  返回枚举的属性：Word应用程序对象上的WindowState属性

 

Select Case app.WindowState
  Case Word.WdWindowState.wdWindowStateMaximize
    MsgBox("Maximized")
  Case Word.WdWindowState.wdWindowStateMinimize
    MsgBox("Minimized")
  Case Word.WdWindowState.wdWindowStateNormal
    MsgBox("Normal")
End Select

 

清单1.7  返回另一个对象模型对象的属性：Word应用程序对象上的ActiveDocument属性

 

Dim myDocument As Word.Document = app.ActiveDocument
MsgBox(myDocument.Name)

列表1.8  可能抛出异常的属性：Word应用程序对象上的ActiveDocument属性

 

Dim myDocument As Word.Document
Try
    myDocument = app.ActiveDocument
    MsgBox(myDocument.Name)
Catch ex As Exception
    MsgBox(String.Format("No active document: {0}", ex.Message)
End Try

 

清单1.9  可以返回的属性：Excel应用程序对象上的ActiveWorkbook属

 

Dim myWorkbook As Excel.Workbook = app.ActiveWorkbook

If myWorkbook Is Nothing Then
  MsgBox("No active workbook")
Else
  MsgBox(myWorkbook.Name)
End If

 

清单1.10  枚举参数并返回对象模型对象的参数化属性：Word应用程序对象上的FileDialog属性

Dim dialog As Office.FileDialog
dialog = app.FileDialog(Office.MsoFileDialogType. _
  msoFileDialogFilePicker)
dialog.Show()

清单1.11  具有可选参数的参数化属性：Excel应用程序对象上的范围属性

' 删除第二个可选参数
Dim myRange As Excel.Range = app.Range("A1")

' 指定第二个可选参数
Dim myRange2 As Excel.Range = app.Range("A1", "B2")

默认参数化属性

 

Dim myWorksheet As Excel.Worksheet
myWorksheet = CType(app.Workbooks.Item(1).Worksheets.Item(1), Excel.Worksheet)

'重写上面的代码

Dim myWorksheet As Excel.Worksheet
myWorksheet = CType(app.Workbooks(1).Worksheets(1), Excel.Worksheet)

 

清单1.12  无参数无返回类型的方法：Word应用对象的激活方法

 

MsgBox("Activating the Word window.")

app.Activate()

清单1.13  具有参数和无返回类型的方法：Word应用程序对象上的ChangeFileOpenDirectory方法

app.ChangeFileOpenDirectory("c:\temp")
MsgBox("Will open out of temp for this session.")

清单1.14  无参数和返回类型的方法：Word应用程序对象的DefaultWebOptions方法

 

Dim options As Word.DefaultWebOptions = app.DefaultWebOptions()
MsgBox(String.Format("Pixels per inch is {0}.", options.PixelsPerInch))

 清单1.15  具有参数和返回类型的方法：Word应用程序对象上的CentimetersToPoints方法

Dim centimeters As Single = 15.0
Dim points As Single = app.CentimetersToPoints(centimeters)
MsgBox(String.Format("{0} centimeters is {1} points.",  centimeters, points))
'将计量单位从厘米转换为磅。1磅=0.035厘米

'将Excel表的左边距设置为5厘米
Worksheets("Sheet1").PageSetup.LeftMargin = Application.CentimetersToPoints(5)

清单1.16  具有可选参数和返回类型的方法：Excel应用程序对象上的CheckSpelling方法

Dim phrase1 As String = "Thes is spelled correctly."
Dim phrase2 As String = "This is spelled correctly AFAIK."

Dim isCorrect1 As Boolean = app.CheckSpelling(phrase1)
Dim isCorrect2 As Boolean = app.CheckSpelling(phrase2, , True)

'用法
expression.CheckSpelling(Word, CustomDictionary, IgnoreUppercase)
 'Word   String 类型（仅与 Application对象一起使用），必需。要检查的单词。 
 'CustomDictionary   Variant 类型，可选。用于表示自定义词典文件名的字符串，如果在主词典中找不到单词，则会到此词典中查找。如果忽略此参数，则将使用当前指定词典。 
 'IgnoreUppercase   Variant 类型，可选。如果为 True，则 Microsoft Excel 忽略那些所有字母都是大写的单词。如果为 False 则 Microsoft Excel 检查那些所有字母都是大写的单词。如果省略该参数，则使用当前设置。

表1.3  Excel应用对象引发的事件

事件的名称	什么时候它被激活
NewWorkbook	当一个工作簿被创建是
SheetActivate	当某个工作表被激活时
SheetBeforeDoubleClick	当某个工作表被双击时
SheetBeforeRightClick	当某个工作表被右击时
SheetCalculate	在某个工作表被重新计算后
SheetChange	当某个工作表单元格被用户改变时
SheetDeactivate	当某个工作表被停用时
SheetFollowHyperlink	当某个工作表中超链接被单击时
SheetPivotTableUpdate	在数据透视表被更新后
SheetSelectionChange	当工作表上的选择更改时
WindowActivate	当工作表窗口被激活时
WindowDeactivate	当工作表窗口被停用时
WindowResize	当工作表窗口调整大小时
WorkbookActivate	当工作簿被激活时
WorkbookAddinInstall	当工作簿作为加载项安装时
WorkbookAddinUninstall	当工作簿作为加载项卸载时
WorkbookAfterXmlExport	在工作簿数据作为XML文件导出后
WorkbookAfterXmlImport	在工作簿中导入XML文件后
WorkbookBeforeClose	工作簿关闭前
WorkbookBeforePrint	在打印工作簿前
WorkbookBeforeSave	在工作簿保存前
WorkbookBeforeXmlExport	工作簿数据作为XML文件导出前
WorkbookBeforeXmlImport	工作簿导入XML文件前
WorkbookDeactivate	当工作簿停用时
WorkbookNewSheet	当在工作簿中创建工作表时
WorkbookOpen	当工作簿打开时
WorkbookPivotTableCloseConnection	当透视报表连接关闭时
WorkbookPivotTableOpenConnection	当透视报表连接打开时
WorkbookSync	当作为文档工作区的一部分的工作簿与服务器上的副本同步时

申明事件处理

 

Public WithEvents app As Excel.Application
'WithEvents表明Excel.Aplication的实例对象是一个可以引发事件的对象

 

Event WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)

Private Sub app_WindowActivate(ByVal Wb As Excel.Workbook,  ByVal Wn As Excel.Window) Handles app.WindowActivate
  MsgBox("The window " & Wn.Caption & " was just activated.")
End Sub

清单1.17  处理Excel应用程序对象的WindowActivate事件的VSTO自定义

Public Class Sheet1

  Public WithEvents app As Excel.Application

  Private Sub app_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window) Handles app.WindowActivate
    MsgBox("The window " & Wn.Caption & " was just activated.")
  End Sub

  Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
    app = Me.Application
  End Sub

End Class

高级主题：动态处理事件

 

AddHandler app.WindowActivate,  AddressOf Me.MyWindowActivateHandler
'AddHandler和RemoveHandler语句将传递要处理的事件以及将处理事件的事件处理程序方法。 当指定事件处理程序方法时，使用AddressOf关键字。 以下代码使用AddHandler动态添加事件处理程序MyWindowActivateHandler来处理应用程序对象的WindowActivate事件：

 

RemoveHandler app.WindowActivate,  AddressOf Me.MyWindowActivateHandler
'删除事件处理方法

Private Sub app_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)
  MsgBox("The window " & Wn.Caption & " was just activated.")
End Sub
'与动态事件处理程序一样，事件处理程序签名必须与事件的预期签名相匹配。 但是，当您动态处理事件时，Handles关键字不会用于事件处理程序签名。 因此，WindowActivate事件的动态事件处理程序看起来像声明性事件处理程序，但省略了Handles子句：

清单1.18  动态添加和删除Excel应用程序对象的WindowActivate事件的事件处理程序的VSTO自定义

 

Public Class Sheet1

  Public app As Excel.Application

  Private Sub MyWindowActivateHandler(ByVal Wb As  Excel.Workbook, ByVal Wn As Excel.Window)
    MsgBox("The window " & Wn.Caption & " was just activated.")
    RemoveHandler app.WindowActivate,  AddressOf Me.MyWindowActivateHandler
  End Sub

  Private Sub Sheet1_Startup(ByVal sender As Object,  ByVal e As System.EventArgs) Handles Me.Startup
    app = Me.Application
    AddHandler app.WindowActivate, AddressOf Me.MyWindowActivateHandler
  End Sub

End Class

'本示例使工作簿窗口激活时最大化。
Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
    Wn.WindowState = xlMaximized
End Sub

清单1.19  一个无法处理CommandBarButton单击事件的类

 

Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core

Class SampleListener
  Private app As Excel.Application

  Public Sub New(ByVal application As Excel.Application)
    app = application
  End Sub

  ' 这里的作用是关联Click事件，但会失败，因为btn没有放在一个更永久的变量中。
  Public Sub ConnectEvents()
    Dim bar As Office.CommandBar = app.CommandBars("Standard")
    Dim btn As Office.CommandBarButton = bar.Controls.Add(1)
    If btn IsNot Nothing Then
      btn.Caption = "My Button"
      btn.Tag = "SampleListener.btn"
      AddHandler btn.Click, AddressOf Me.btn_Click
    End If
  End Sub

  ' Click事件永远不会到达此处理程序.
  Public Sub btn_Click(ByVal ctrl As Office.CommandBarButton,  ByRef cancelDefault As Boolean)
    MessageBox.Show("Button was clicked")
  End Sub

End Class

 清单1.20  一个无法处理Outlook检查器对象的NewInspectorEvent的类

 

Imports Outlook = Microsoft.Office.Interop.Outlook

Class SampleListener
  Private app As Outlook.Application

  Public Sub New(ByVal application As Outlook.Application)
    app = application
  End Sub

  ' This will appear to connect to the NewInspector event, but
  ' will fail because Inspectors is not put in a more permanent
  ' variable.
  Public Sub ConnectEvents()
    AddHandler app.Inspectors.NewInspector, _
      AddressOf Me.MyNewInspectorHandler
  End Sub

  ' The NewInspector event will never reach this handler.
  Public Sub MyNewInspectorHandler(ByVal inspector As Outlook
.Inspector)
    MessageBox.Show("New inspector.")
  End Sub
End Class

 

清单1.21  一个类成功处理CommandBarButton单击事件，因为它将CommandBarButton对象存储在一个类成员变量中

 

Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core

Class SampleListener
  Private app As Excel.Application
  Private myBtn As Office.CommandBarButton

  Public Sub New(ByVal application As Excel.Application)
    app = application
  End Sub

  Public Sub ConnectEvents()
    Dim bar As Office.CommandBar = app.CommandBars("Standard")
    myBtn = bar.Controls.Add(1)
    If myBtn IsNot Nothing Then
      myBtn.Caption = "My Button"
      myBtn.Tag = "SampleListener.btn"
      AddHandler myBtn.Click, AddressOf Me.myBtn_Click
    End If
  End Sub

  Public Sub myBtn_Click(ByVal ctrl As Office.CommandBarButton, ByRef cancelDefault As Boolean)

    MessageBox.Show("Button was clicked")
  End Sub

End Class

清单1.22   一个类成功处理Outlook检查器对象的NewInspector事件，因为它将检查器对象存储在一个类成员变量中

Imports Outlook = Microsoft.Office.Interop.Outlook

Class SampleListener
  Private app As Outlook.Application
  Private myInspectors As Outlook.Inspectors

  Public Sub New(ByVal application As Outlook.Application)
    app = application
  End Sub

  Public Sub ConnectEvents()
    myInspectors = app.Inspectors
    AddHandler myInspectors.NewInspector, _
      AddressOf Me.MyNewInspectorHandler
  End Sub

  Public Sub MyNewInspectorHandler( _
    ByVal inspector As Outlook.Inspector)
    MessageBox.Show("New inspector.")
  End Sub
End Class