I am trying to build a code that opens a word template, finds all instances of codes that are listed in an excel sheet, and replaces them with values that are in the column next to the codes in the excel sheet.
The code also creates a popup so the user can select the word template they want to use, then automatically saves and closes the document.
I’m getting an error message that there is a “Type Mismatch” on the line that reads
.ActiveDocument.Words(i) = Replace(.ActiveDocument.Words(i), ActiveSheet.Cells(j, 2).Value, ActiveSheet.Cells(j, 3).Value)
I’m new to VBA, can someone help me debug (and/or improve) the complete code below?
Thank you!
Option Explicit
Sub WriteWordTemplate()
Dim wdApp As New Word.Application
'TemplatePath is used as starting point for selecting Word template
Dim TemplatePath As String
'WordTemplateFolder is a variable on the Inputs sheet. This can be changed!
Dim WordTemplateFolder As String
WordTemplateFolder = Worksheets("Inputs").Range("B3")
'This allows the user to select a word template
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.InitialFileName = WordTemplateFolder
If .Show = True Then
TemplatePath = .SelectedItems(1)
End If
End With
'This code opens your selected document
Set wdApp = Word.Application
With wdApp
.Documents.Open (TemplatePath)
Dim TemplateName As String
TemplateName = .ActiveDocument.Name
'This code should run the find and replace function. It's glitching though.
Dim i As Integer, j As Integer
Dim lastRow As Long
'Set lastRow = 66
'loop through all words in Word Document
For i = 1 To .ActiveDocument.Words.Count - 1 Step 1
'Loop through cells in Excel File
For j = 12 To 66 Step 1
'Replace instances of codes (from excel column B) in Word doc with values from excel column C
.ActiveDocument.Words(i) = Replace(.ActiveDocument.Words(i), ActiveSheet.Cells(j, 2).Value, ActiveSheet.Cells(j, 3).Value)
Next j
Next i
'Saves document with time date, time, & sheet name & template name.docx
Dim SaveName As String
SaveName = ActiveSheet.Range("C4") & "" & Format(Now, "yyyy.mm.dd hh-mm-ss - ") & ActiveSheet.Name & " - " & TemplateName & ".docx"
.ActiveDocument.SaveAs2 SaveName
.ActiveDocument.Close
.Quit
End With
Set wdApp = Nothing
End Sub
SDalal is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.