MS VBA 和 XPath 2.0

自动化办公是现代工作中不可或缺的一部分，它可以帮助我们提高工作效率、减少重复劳动。在Microsoft Office中，VBA（Visual Basic for Applications）是一种强大的编程语言，可以用于自动化处理Excel、Word等文档。而XPath 2.0是一种用于在XML文档中定位和提取数据的查询语言。本文将介绍如何结合MS VBA和XPath 2.0来实现自动化办公，并通过案例代码进行说明。

vba
Sub AutoProcessExcelData()
    Dim xmlDoc As MSXML2.DOMDocument
    Dim xmlNodes As MSXML2.IXMLDOMNodeList
    Dim xmlNode As MSXML2.IXMLDOMNode
    Dim excelApp As Excel.Application
    Dim excelWorkbook As Excel.Workbook
    Dim excelWorksheet As Excel.Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' 创建XML文档对象
    Set xmlDoc = New MSXML2.DOMDocument
    
    ' 打开Excel应用
    Set excelApp = New Excel.Application
    Set excelWorkbook = excelApp.Workbooks.Open("C:\Path\To\Your\Excel\File.xlsx")
    Set excelWorksheet = excelWorkbook.Worksheets("Sheet1")
    
    ' 获取数据表格的最后一行
    lastRow = excelWorksheet.Cells(excelWorksheet.Rows.Count, "A").End(xlUp).Row
    
    ' 遍历每一行数据
    For i = 2 To lastRow
        ' 创建XML节点
        Set xmlNode = xmlDoc.createElement("Student")
        
        ' 设置学生姓名
        xmlNode.setAttribute "Name", excelWorksheet.Cells(i, 1).Value
        
        ' 设置学生年龄
        xmlNode.setAttribute "Age", excelWorksheet.Cells(i, 2).Value
        
        ' 设置学生成绩
        xmlNode.setAttribute "Score", excelWorksheet.Cells(i, 3).Value
        
        ' 将节点添加到XML文档中
        xmlDoc.appendChild xmlNode
    Next i
    
    ' 保存XML文档
    xmlDoc.Save "C:\Path\To\Your\XML\File.xml"
    
    ' 关闭Excel应用
    excelWorkbook.Close SaveChanges:=False
    excelApp.Quit
    
    ' 释放对象
    Set excelWorksheet = Nothing
    Set excelWorkbook = Nothing
    Set excelApp = Nothing
    Set xmlNode = Nothing
    Set xmlNodes = Nothing
    Set xmlDoc = Nothing
    
    MsgBox "Excel数据处理完成！"
End Sub

vba
Sub ExtractHeadingsFromWordDocument()
    Dim wordApp As Word.Application
    Dim wordDocument As Word.Document
    Dim xmlNodes As MSXML2.IXMLDOMNodeList
    Dim xmlNode As MSXML2.IXMLDOMNode
    Dim i As Long
    Dim outputFile As String
    Dim fileNumber As Integer
    
    ' 打开Word应用
    Set wordApp = New Word.Application
    Set wordDocument = wordApp.Documents.Open("C:\Path\To\Your\Word\File.docx")
    
    ' 使用XPath查询语言提取所有标题
    Set xmlNodes = wordDocument.SelectNodes("//strong")
    
    ' 设置输出文件路径
    outputFile = "C:\Path\To\Your\Output\File.txt"
    
    ' 打开输出文件
    fileNumber = FreeFile
    Open outputFile For Output As fileNumber
    
    ' 遍历标题节点，并将其写入输出文件
    For i = 0 To xmlNodes.Length - 1
        Print #fileNumber, xmlNodes.Item(i).Text
    Next i
    
    ' 关闭输出文件
    Close fileNumber
    
    ' 关闭Word应用
    wordDocument.Close SaveChanges:=False
    wordApp.Quit
    
    ' 释放对象
    Set xmlNode = Nothing
    Set xmlNodes = Nothing
    Set wordDocument = Nothing
    Set wordApp = Nothing
    
    MsgBox "标题提取完成！"
End Sub