How to automatically generate word reports in excel?-excel-php.cn

#How to automatically generate word reports in Excel?

How to automatically generate word reports in Excel:

1. Make a contract template file and replace the contract variables with special variables. The diagram is as follows:

How to automatically generate word reports in excel?

2. Add the main content data of the contract in EXCEL, the diagram is as follows:

How to automatically generate word reports in excel?

3 . Add an Active X button control in EXCEL and modify its properties according to your own needs.

How to automatically generate word reports in excel?

4. Open the VBA editor and add a project reference.

The specific operation process is: select "Tools" - "Reference", then open the load file selection box, select the "Microsoft Word16.0 Object Library" project, as shown below:

How to automatically generate word reports in excel?

Here, it is particularly important to note that the Word project must be referenced, otherwise VBA will not be able to call the Word substitution function when performing variable substitution later.

5. Write the following code under the button control and save the EXCEL file as XLSM:

Private Sub cmd_makedoc_Click()On Error GoTo Err_cmdExportToWord_Click Dim objApp As Object 'Word.Application Dim objDoc As Object 'Word.Document Dim strTemplates As String '模板文件路径名 Dim strFileName As String '将数据导出到此文件 Dim i As Integer Dim contact_NO As String Dim side_A As String Dim side_B As String i = ActiveCell.Row contact_NO = Cells(i, 1) side_A = Cells(i, 2) side_B = Cells(i, 3) With Application.FileDialog(msoFileDialogFilePicker) .Filters.Add "word文件", "*.doc*", 1 .AllowMultiSelect = False If .Show Then strTemplates = .SelectedItems(1) Else Exit Sub End With '通过文件对话框生成另存为文件名 With Application.FileDialog(msoFileDialogSaveAs) '.InitialFileName = CurrentProject.Path & "\" & contact_NO & ".doc" .InitialFileName = contact_NO & ".doc" If .Show Then strFileName = .SelectedItems(1) Else Exit Sub End With '文件名必须包括“.doc”的文件扩展名，如没有则自动加上 If Not strFileName Like "*.doc" Then strFileName = strFileName & ".doc" '如果文件已存在，则删除已有文件 If Dir(strFileName) <> "" Then Kill strFileName '打开模板文件 Set objApp = CreateObject("Word.Application") objApp.Visible = True Set objDoc = objApp.Documents.Open(strTemplates, , False) '开始替换模板预置变量文本 With objApp.Application.Selection .Find.ClearFormatting .Find.Replacement.ClearFormatting With .Find .Text = "{$合同编号}" .Replacement.Text = contact_NO End With .Find.Execute Replace:=wdReplaceAll With .Find .Text = "{$甲方}" .Replacement.Text = side_A End With .Find.Execute Replace:=wdReplaceAll With .Find .Text = "{$乙方}" .Replacement.Text = side_B End With .Find.Execute Replace:=wdReplaceAll End With '将写入数据的模板另存为文档文件 objDoc.SaveAs strFileName objDoc.Saved = True MsgBox "合同文本生成完毕！", vbYes + vbExclamationExit_cmdExportToWord_Click: If Not objDoc Is Nothing Then objApp.Visible = True Set objApp = Nothing Set objDoc = Nothing Set objTable = Nothing Exit SubErr_cmdExportToWord_Click: MsgBox Err.Description, vbCritical, "出错" Resume Exit_cmdExportToWord_ClickEnd Sub

Copy after login