如何将多个 Word 文件中的表格复制到 Excel 中的单独工作表，并将工作表命名为 Word 文档的名称? [英] How to copy tables from multiple Word files to separate worksheets in Excel, naming the worksheet the name of the Word doc?

问题描述

我使用下面的 VBA 宏将多个 Word 文档中的多个表格放入 Excel 中的一个工作表中.

I have used the VBA macro below to put multiple tables from multiple Word documents into one worksheet in Excel.

我希望将每个不同 Word 文档中的多个表格放入不同的工作表中，这些工作表以 Word 文档的名称命名.

I want the multiple tables from each different Word doc to go into different worksheets with the worksheets named the name of the Word doc.

Sub ImportWordTable()

    Dim WordApp As Object
    Dim WordDoc As Object
    Dim arrFileList As Variant, FileName As Variant
    Dim tableNo As Integer                            'table number in Word

    Dim tableStart As Integer
    Dim tableTot As Integer
    Dim Target As Range

    'On Error Resume Next

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

    If Not IsArray(arrFileList) Then Exit Sub         '(user cancelled import file browser)

    Set WordApp = CreateObject("Word.Application")
    WordApp.Visible = True

    Range("A:AZ").ClearContents
    Set Target = Range("A1")

    For Each FileName In arrFileList
        Set WordDoc = WordApp.Documents.Open(FileName, ReadOnly:=True)

        With WordDoc
            tableNo = WordDoc.tables.Count
            tableTot = WordDoc.tables.Count
            If tableNo = 0 Then
                MsgBox WordDoc.Name & " contains no tables", vbExclamation, "Import Word Table"

            ElseIf tableNo > 1 Then
                tableNo = InputBox(WordDoc.Name & " contains " & tableNo & " tables." & vbCrLf & _
                                   "Enter the table to start from", "Import Word Table", "1")
            End If

            For tableStart = 1 To tableTot
                With .tables(tableStart)
                    .Range.Copy
                    'Target.Parent.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
                    Target.Activate
                    ActiveSheet.Paste

                    Set Target = Target.Offset(.Rows.Count + 2, 0)
                End With
            Next tableStart

            .Close False
        End With

    Next FileName

    WordApp.Quit

    Set WordDoc = Nothing
    Set WordApp = Nothing
End Sub

推荐答案

试试下面的宏.它允许您选择源文件夹.它为每个文档创建一个新的工作表，并输出该文档中的所有表格，一个在另一个下方，中间有一个空行.除了文本换行，表格格式尽可能保留.

Try the following macro. It allows you to choose the source folder. It creates a new worksheet for each document and outputs all tables from that document one below the other, with an empty row in between. Except for text wrapping, table formatting is preserved as much as possible.

Sub GetTableData()
'Note: this code requires a reference to the Word object model.
'See under the VBE's Tools|References.
Application.ScreenUpdating = False
Dim wdApp As New Word.Application, wdDoc As Word.Document, wdTbl As Word.Table
Dim strFolder As String, strFile As String, WkBk As Workbook, WkSht As Worksheet, r As Long
strFolder = GetFolder: If strFolder = "" Then GoTo ErrExit
Set WkBk = ActiveWorkbook
'Disable any Word Alerts
wdApp.DisplayAlerts =wdAlertsNone
'Disable any auto macros in the documents being processed
wdApp.WordBasic.DisableAutoMacros
strFile = Dir(strFolder & "*.doc", vbNormal)
While strFile <> ""
  Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "" & strFile, ReadOnly:=True, AddToRecentFiles:=False, Visible:=False)
  Set WkSht = WkBk.Sheets.Add
  WkSht.Name = Split(strFile, ".doc")(0)
  With wdDoc
    For Each wdTbl In .Tables
      With wdTbl.Range.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = "[^13^l]"
        .Replacement.Text = "¶"
        .Forward = True
        .Wrap = wdFindStop
        .Format = False
        .MatchWildcards = True
        .Execute Replace:=wdReplaceAll
      End With
      r = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
      If r > 1 Then r = r + 2
      wdTbl.Range.Copy
      WkSht.Paste Destination:=WkSht.Range("A" & r)
    Next
    WkSht.UsedRange.Replace What:="¶", Replacement:=Chr(10), LookAt:=xlPart, SearchOrder:=xlByRows
    .Close SaveChanges:=False
  End With
  strFile = Dir()
Wend
ErrExit:
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing: Set WkBk = Nothing
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
    Dim oFolder As Object
    GetFolder = ""
    Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
    If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
    Set oFolder = Nothing
End Function

这篇关于如何将多个 Word 文件中的表格复制到 Excel 中的单独工作表，并将工作表命名为 Word 文档的名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！