Import Excel Data to Word template

1. Excel

Step 1: Set the template file path: It can be stored in the. xls M file.

Step 2: Add a button and add a macro to the button-defined click event.

Macro function:

Sub do_api ()

Dim data_sheet
Dim I, j
Dim find_str, repl_str
Dim lq_string, rq_string

Dim data_file, template_file, target_file
Dim found

Data_file = thisworkbook. Path & "\" & sheet1.cells (3, 2)
Template_file = thisworkbook. Path & "\" & sheet1.cells (3, 3)

Lq_string = left (sheet1.cells (1, 4), 1)
Rq_string = right (sheet1.cells (1, 4), 1)


Application. workbooks. Open data_file


Found = false
For I = 1 to activeworkbook. Sheets. Count
If instr (activeworkbook. Sheets (I). Name, "performance_sheetname")> 0 then
Set data_sheet = activeworkbook. Sheets (I)
Found = true
Exit
End if
Next

If not found then msgbox "sheet not found! ": Exit sub

Dim wdapp as new word. Application
Wdapp. Visible = true


For I = 2 to data_sheet.usedrange.rows.count
Wdapp. Documents. Open template_file
For j = 1 to data_sheet.usedrange.columns.count

Find_str = lq_string & replace (data_sheet.cells (1, J), vblf, "") & rq_string
Repl_str = data_sheet.cells (I, j)
Debug. Print "replace" & find_str & "with" & repl_str

If J = column_index1 or J = column_index2 then
Wdapp. activewindow. activepane. View. seekview = wdseekcurrentpageheader
Find_str = Replace (data_sheet.cells (1, J), vblf ,"")
Else
Wdapp. activewindow. activepane. View. seekview = wdseekmaindocument
End if


Wdapp. selection. Find. clearformatting
Wdapp. selection. Find. Replacement. clearformatting
With wdapp. selection. Find
. Text = find_str
. Replacement. Text = repl_str
. Forward = true
. Wrap = 1 'wdfindcontinue
. Format = false
. Matchcase = false
. Matchwholeword = false
. Matchbyte = false
. Matchallwordforms = false
. Matchsoundslike = false
. Matchwildcards = false
. Matchfuzzy = false
End
Wdapp. selection. Find. Execute Replace: = 2 'wdreplaceall


Next
Target_file = "sym001 _" & data_sheet.cells (I, index1) & "_" & data_sheet.cells (I, index2) & ". Doc"
Target_file = thisworkbook. Path & "\" & target_file

If Dir (target_file) <> "" Then kill target_file
Wdapp. activedocument. saveas target_file
Wdapp. activedocument. Close false

Next

Wdapp. Quit false
Set wdapp = nothing

Activeworkbook. Close
Msgbox "done! "
End sub

2. Add related mergefield to the Word template.