VBA将Word文档内容逐行写入Excel

发布于:2025-04-09 ⋅ 阅读:(395) ⋅ 点赞:(0)

     如果你需要将Word文档的内容导入Excel工作表来进行数据加工,使用下面的代码可以实现:

Sub ImportWordToExcel()
    Dim wordApp As Word.Application
    Dim wordDoc As Word.Document
    Dim excelSheet As Worksheet
    Dim filePath As Variant
    Dim i As Long
    Dim para As Word.Paragraph
    Dim lineText As String
    
    ' 设置Excel工作表
    Set excelSheet = ThisWorkbook.Sheets("Sheet1") ' 修改为你的工作表名称
    i = 1 ' 从A1开始写入
    
    ' 让用户选择Word文档
    filePath = Application.GetOpenFilename("Word Documents (*.docx;*.doc), *.docx", , "选择要导入的Word文档")
    If filePath = False Then Exit Sub ' 用户取消选择
    
    On Error GoTo ErrorHandler
    
    ' 创建Word应用
    Set wordApp = New Word.Application
    wordApp.Visible = False ' 隐藏Word界面
    
    ' 打开Word文档
    Set wordDoc = wordApp.Documents.Open(filePath)
    
    ' 遍历每个段落
    For Each para In wordDoc.Paragraphs
        lineText = Replace(para.Range.Text, Chr(13), "") ' 移除段落标记
        lineText = Trim(lineText)
        If lineText <> "" Then
            ' 可选:按手动换行符分割行(例如Shift+Enter)
            Dim lines As Variant
            lines = Split(lineText, Chr(11)) ' Chr(11)代表手动换行符
            For Each line In lines
                If Trim(line) <> "" Then
                    excelSheet.Cells(i, 1).Value = Trim(line)
                    i = i + 1
                End If
            Next line
        End If
    Next para
    
Cleanup:
    ' 关闭并释放Word对象
    If Not wordDoc Is Nothing Then
        wordDoc.Close SaveChanges:=False
        Set wordDoc = Nothing
    End If
    If Not wordApp Is Nothing Then
        wordApp.Quit
        Set wordApp = Nothing
    End If
    Exit Sub
    
ErrorHandler:
    MsgBox "错误 " & Err.Number & ": " & Err.Description, vbCritical, "错误"
    Resume Cleanup
End Sub

        注意:使用以上代码需要引用“Microsoft Word xx.0 Object Library”库,在Excel VBA编辑器中,通过点击“工具”→“引用”来勾选。


网站公告

今日签到

点亮在社区的每一天
去签到