计量标准自查表批量生成
计量标准自查表批量生成VBA思路在Excel表中列出计量标准名称、计量标准考核证书编号，打开word自查表,在表格中填入对应信息，保存到自查表文件夹中！使用For循环，批量生成！
Sub 导出到Word模板中()
Dim Sht_Workbook As Workbook
Dim Sht_Worksheet As Worksheet
Dim Str_standardName As String
Dim Str_DepartmentName As String
Dim Str_standardNumber As String
Dim WordApp As Object
Dim WordDocNew As Object
Dim filePath As String
Dim savePath As String
Dim i As Long
' 设置当前工作簿和工作表
Set Sht_Workbook = ThisWorkbook
Set Sht_Worksheet = Sht_Workbook.Sheets(1)
' 创建Word应用程序实例
On Error Resume Next
Set WordApp = GetObject(, "Word.Application")
If WordApp Is Nothing Then
Set WordApp = CreateObject("Word.Application")
End If
On Error GoTo 0
WordApp.Visible = True ' 可选：使Word可见
' Word模板文件路径
filePath = ThisWorkbook.Path & "\" & "自查表模板.docx"
' 遍历Excel中的行
For i = 2 To 150 ' 根据需要调整这个范围
' 读取单元格的值
Str_standardName = Sht_Worksheet.Cells(i, 3).Value
Str_standardNumber = Sht_Worksheet.Cells(i, 4).Value
Str_DepartmentName = Sht_Worksheet.Cells(i, 12).Value
' 基于模板创建一个新文档
savePath = ThisWorkbook.Path & "\自查表\自查表-" & Str_DepartmentName & "-" & Str_standardName & ".docx"
Set WordDocNew = WordApp.Documents.Add(Template:=filePath) ' 使用模板添加新文档
' 在新文档的表格中填充数据
With WordDocNew.Tables(1)
.Cell(1, 4).Range.Text = Str_standardName ' 假设表格第一行是标题，我们从第二行开始填充数据（如果需要）
' .Cell(2, 2).Range.Text = Str_DepartmentName
.Cell(2, 4).Range.Text = Str_standardNumber
' 注意：这里可能需要根据你的实际表格结构调整行和列索引
' ... 其他必要的填充操作
End With
' 保存新文档
WordDocNew.SaveAs (savePath)
savePath = ThisWorkbook.Path & "\自查表\自查表-" & Str_DepartmentName & "-" & Str_standardName & ".pdf"
WordDocNew.SaveAs savePath, FileFormat:=wdFormatPDF
' 关闭新文档（可选）
WordDocNew.Close SaveChanges:=False ' 因为我们已经用SaveAs2保存了，所以这里不需要再次保存
' 清理（可选，但在这个循环中很重要以避免内存泄漏）
Set WordDocNew = Nothing
Next i
' 清理（可选，但在宏结束时是个好习惯）
Set WordApp = Nothing
End Sub
谢谢，学习了
页:
[1]