Word 插件实现读取excel自动填写

日常工作中碰到需要将EXCEL的对应数据记录填写到word文档对应的位置，人工操作的方式是：
打开exel表—>查找对应报告号的行—>逐列复制excel表列单元格内容到WORD对应的位置（如下图标注所示）

这种方法耗时且容易出错。实际上如果使用VBA开发WORD插件，编写代替人手操作的自动程序可以实现自动填写（秒填的感觉很爽），可以一劳永逸解决这样的困扰。
实现的代码如下：
Sub ReadExcelData()
Dim xlApp As Object
Dim xlBook As Object
arr_excel = Array(“SAMPLE CODE”, “TRI PRODUIT”, “TRI COMPOSANT”, “TYPE”, “COMPONENT”, “Color”, “Description”, “FP MODEL”, “FP MATERIAL”, “FP Color”, “DESCRIZIONE”, _
“FP SUPPLIER”, “USE”, “COMPOSIZIONE”, “FORNITORE”, “POIDS G / M2”, “INFLA”, “SHIPPING DATE TO CHINA”, “RECEPTION Date”, “CELINE PACKAGE”)
arr_E = Array(“B”, “”, “D”, “E”, “F”, “G”, “H”, “I”, “J”, “K”, “L”, “M”, “N”, “O”, “P”, “”, “R”, “S”, “T”, “U”, “V”, “W”)

arr_Word = Array(“CERTEST REFERENCE”, “”, “TRI PRODUIT”, “TRI COMPOSANT”, “TYPE”, “COMPONENT”, “Color”, “Description”, “FP MODEL”, “FP MATERIAL”, “FP Color”, “Description PROJECT”, _
“FP SUPLIER”, “USE”, “COMPOSITION”, “”, “SUPPLIER”, “POIDS”, “INFLA”, “RECEPTION Date”, “SHIPPING DATE TO CHINA”, “Test PACKAGE”)
rp = Left(ActiveDocument.Name, 11)
Set xlApp = CreateObject(“Excel.Application”)
filepath = “K:\XX\xx\xx” 'excel表坐在的路径
fn = Dir(filepath & “xxxx” & “*.xlsx”)
Set xlBook = xlApp.Workbooks.Open(filepath & fn)
With xlBook.Sheets(1)
rws = .Cells(.rows.Count, “a”).End(-4162).Row
For i = 2 To rws
If InStr(.Cells(i, 1).value, rp) > 0 Then
ReDim arr(22)
For j = 0 To 22
arr(j) = .Cells(i, j 2)
Next
Exit For
End If
Next
End With
xlBook.Close False
Set xlBook = Nothing
Set xlApp = Nothing

'MsgBox UBound(arr)
For k = 0 To UBound(arr) - 1
If arr_Word(k) <> “” Then
Call infoFill(arr_Word(k), arr(k))
End If
Next
End Sub

Private Sub infoFill(kw, res)
Dim para As Paragraph
For Each para In ActiveDocument.Paragraphs
If InStr(UCase(para.Range.Text), UCase(kw)) > 0 Then
If res = “” Then
para.Next.Next.Range = “/”
Else
para.Next.Next.Range = res
End If
If InStr(UCase(para.Range.Text), “CERTEST REFERENCE”) > 0 Then
para.Next.Next.Range = para.Next.Next.Range & Split(para.Next.Next.Range, “.”)(0) & “.02”
End If
Exit Sub
End If
Next
将插件安装在WORD的NORML文件，实现点击按钮即可实现excel内容的自动填写.

给按钮添加图标，这样在WORD的操作界面就可以看到程序执行的插件。

这样插件就完成了，每次点击按钮就可以秒速完成填写。

这篇好文章是转载于：学新通技术网