将数据从大型Excel文件传输到word文档中的多个表

Question

我正试图将数据从一个大型excel表转移到word文档中的小表中。我想通过匹配行中的第一列，然后将列中的单元格复制到行的右侧来完成此操作。

例如，以下是excel文件中的表格：

名字姓氏安

公司
苹果	Tom
谷歌	Anny
亚马逊	驱动程序	Ted

对于第一种方法，您可以在MS Word中使用此代码，在那里您可以填充表格。代码未优化，因此省略了错误检查。它要求命名范围"MyData"存在，并且正好包含三列[company，name1，name2]

'Add reference to the 'Microsoft Excel 16.0 Object Library' is required
Sub FillTablesFromExcel()

Dim doc As Word.Document
Set doc = ThisDocument

Dim data
data = ReadDataFromExcel

Dim t As Word.Table
For Each t In doc.Tables
If t.Columns.Count <> 3 Then Exit For

Dim r As Word.Row
For Each r In t.Rows
Dim txt As String
txt = r.Cells(1).Range.Text
company = Trim(Left(txt, Len(txt) - 2))

For i = 1 To UBound(data)
comp = data(i, 1)
name1 = data(i, 2)
name2 = data(i, 3)

If company = comp Then
r.Cells(2).Range.Text = name1
r.Cells(3).Range.Text = name2
End If
Next
Next
Next
End Sub
Function ReadDataFromExcel()
Dim xlsWorkbook As Excel.Workbook
Set xlsWorkbook = Excel.Workbooks.Open("C:PathToTheFile.xlsx", ReadOnly:=True)

Dim xlsSheet As Excel.Worksheet
Set xlsSheet = xlsWorkbook.Sheets(1)

Dim dataRange As Excel.Range
Set dataRange = xlsSheet.Range("MyData")

Value2 = dataRange.Value2

Call xlsWorkbook.Close(False)
ReadDataFromExcel = Value2
End Function