将 Word 表格转换为 Excel 数组

问题描述

我正在尝试将 Word 表格传输到 Excel - 这已经完成 此处 - 此外，在传输过程中，我只想保留包含某些内容的行，并希望在将表格粘贴到 Excel 之前对其进行整形.我认为这可以通过首先将每个表转换为 Excel 数组，然后根据需要修改数组，然后再将其粘贴到指定范围来完成.然而，我对 Word VBA 不太熟悉，而且我发现这项任务非常困难.我从这里的代码开始，我在上面引用的帖子中找到了它.

I am trying to transfer Word tables to Excel - this has already been done here - and in addition, during the transfer I'd like to keep only rows that contain certain content, and would like to reshape the table before pasting it into Excel. I thought this could be done by converting each table first into an Excel array and then modifying the array as needed before pasting it to a specified range. Yet, I am not so familiar with Word VBA and I am finding this task pretty hard. I am starting from this code here, which I found at the post referenced above.

Option Explicit

Sub ImportWordTable()

Dim wdDoc As Object
Dim wdFileName As Variant
Dim tableNo As Integer 'table number in Word
Dim iRow As Long 'row index in Excel
Dim iCol As Integer 'column index in Excel
Dim resultRow As Long
Dim tableStart As Integer
Dim tableTot As Integer

On Error Resume Next

ActiveSheet.Range("A:AZ").ClearContents

wdFileName = Application.GetOpenFilename("Word files (*.docx),*.doc", , _
"Browse for file containing table to be imported")

If wdFileName = False Then Exit Sub '(user cancelled import file browser)

Set wdDoc = GetObject(wdFileName) 'open Word file

With wdDoc
     tableTot = wdDoc.tables.Count
    If tableTot = 0 Then
        MsgBox "This document contains no tables", _
        vbExclamation, "Import Word Table"
    End If


    For tableStart = 1 To tableTot
        With .tables(tableStart)
            'copy cell contents from Word table cells to Excel cells
            For iRow = 1 To .Rows.Count
                For iCol = 1 To .Columns.Count
                    Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
                Next iCol
                resultRow = resultRow + 1
            Next iRow
        End With
        resultRow = resultRow + 1
    Next tableStart
End With

End Sub

我想我应该改变这个块以获得我正在寻找的东西.

I think I should change this chunk to obtain what I am looking for.

For tableStart = 1 To tableTot
            With .tables(tableStart)
                'copy cell contents from Word table cells to Excel cells
                For iRow = 1 To .Rows.Count
                    For iCol = 1 To .Columns.Count
                        Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
                    Next iCol
                    resultRow = resultRow + 1
                Next iRow
            End With
            resultRow = resultRow + 1
        Next tableStart
    End With

有人可以帮我解决这个问题吗?如果需要，我可以提供更多详细信息.非常感谢！

Can someone help me with this? I can provide more details if needed. Many thanks!

里卡多

推荐答案

如果您只想复制某些行:

If you want to copy only certain rows:

For tableStart = 1 To tableTot
    With .tables(tableStart)
        For iRow = 1 To .Rows.Count
            v = WorksheetFunction.Clean(.cell(iRow, 1).Range.Text)
            If v = "A" Or v = "B" Or v = "C" Then
                For iCol = 1 To .Columns.Count
                    Cells(resultRow, iCol) = WorksheetFunction.Clean( _
                                             .cell(iRow, iCol).Range.Text)
                Next iCol
                resultRow = resultRow + 1
            End If
        Next iRow
    End With
    resultRow = resultRow + 1
Next tableStart

这篇关于将 Word 表格转换为 Excel 数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！