用VBA写代码批量提取excel表格数据到word
时间: 2024-05-19 12:14:13 浏览: 19
Sub ExtractExcelDataToWord()
' 声明变量
Dim excelFilePath As String
Dim wordFilePath As String
Dim wordApp As Object
Dim wordDoc As Object
Dim excelApp As Object
Dim excelWorkbook As Object
Dim excelWorksheet As Object
Dim tableRange As Object
Dim tableData As Variant
Dim i As Integer
Dim j As Integer
Dim rowCount As Integer
Dim columnCount As Integer
' 设置文件路径
excelFilePath = "D:\data.xlsx"
wordFilePath = "D:\output.docx"
' 创建Word应用程序
Set wordApp = CreateObject("Word.Application")
' 打开Word文档
Set wordDoc = wordApp.Documents.Open(wordFilePath)
' 创建Excel应用程序
Set excelApp = CreateObject("Excel.Application")
' 打开Excel工作簿
Set excelWorkbook = excelApp.Workbooks.Open(excelFilePath)
' 指定工作表
Set excelWorksheet = excelWorkbook.Worksheets(1)
' 获取表格区域
Set tableRange = excelWorksheet.UsedRange
' 获取表格数据
tableData = tableRange.Value
' 获取表格行数和列数
rowCount = UBound(tableData, 1)
columnCount = UBound(tableData, 2)
' 在Word文档中创建表格
wordDoc.Tables.Add Range:=wordDoc.Range(0, 0), NumRows:=rowCount, NumColumns:=columnCount
' 将表格数据添加到Word表格中
For i = 1 To rowCount
For j = 1 To columnCount
wordDoc.Tables(1).Cell(i, j).Range.Text = tableData(i, j)
Next j
Next i
' 保存Word文档
wordDoc.Save
' 关闭Word文档和应用程序
wordDoc.Close
wordApp.Quit
' 关闭Excel工作簿和应用程序
excelWorkbook.Close
excelApp.Quit
' 释放对象
Set wordDoc = Nothing
Set wordApp = Nothing
Set excelWorksheet = Nothing
Set excelWorkbook = Nothing
Set excelApp = Nothing
' 提示完成
MsgBox "数据已提取到Word文档中。"
End Sub
相关推荐
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![-](https://csdnimg.cn/download_wenku/file_type_column_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)