如何用vba把word转换成excel的b列

zzpsx

如何用vba把word文档转换成excel，文字放到b列.zip (14.82 KB, 下载次数: 6)

2024-11-16 19:05 上传

点击文件名下载附件

想要的效果

zzpsx

我自己写的，不能保留原文字的颜色和下划线斜体等格式
列宽不能自适应
Sub ExportWordToExcelBColumn()
    Dim wdDoc As Document
    Dim wdRange As Range
    Dim xlApp As Object
    Dim xlWorkBook As Object
    Dim xlWorkSheet As Object
    Dim i As Long
    Dim lineText As String
    Dim lines() As String
   
    ' 获取当前Word文档
    Set wdDoc = ActiveDocument
    ' 获取整个文档内容
    Set wdRange = wdDoc.Content
   
    ' 创建Excel应用程序对象
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
   
    ' 创建一个新的工作簿
    Set xlWorkBook = xlApp.Workbooks.Add
    Set xlWorkSheet = xlWorkBook.Sheets(1)
   
    ' 将Word文档内容按行分割
    lineText = wdRange.Text
    ' 移除开头的段落标记和尾部的回车符
    lineText = Replace(lineText, Chr(13) & Chr(7), "")
    lineText = Replace(lineText, Chr(13), vbCrLf)
    lines = Split(lineText, vbCrLf)
   
    ' 将每一行写入Excel的B列
    For i = LBound(lines) To UBound(lines)
        xlWorkSheet.Cells(i + 1, 2).Value = Trim(lines(i))
    Next i
   
    ' 显示Excel并调整窗口
    xlApp.Visible = True
    xlApp.WindowState = xlNormal
   
    ' 清理对象
    Set xlWorkSheet = Nothing
    Set xlWorkBook = Nothing
    Set wdRange = Nothing
    Set wdDoc = Nothing
End Sub

taller

zzpsx

taller 发表于 2024-11-17 01:11

谢谢高手，这个代码很好。太厉害了！