Excel to Word VBA Export - Word Documents Not Being Created

Question

I'm running an Excel to Word Export and I cannot create / save new documents based on the template. Each loop will reopen the word template, replaces the <<>> values in the template, and then moves on the next.

(Background - I have a table in Excel consisting 32 rows and 70 columns. I've created a corresponding word template consisting of values to replace from the excel sheet (for instance, <>). On the run, It exports values based on corresponding tags (for instance, <>) in the Excel sheet to the Word Doc). It seems to be working until it gets to WordDoc.SaveAs Filename

The error I get is

Do you want to save your document as the template name? yes / no

it stops there and does not create templates but only changes the template file.

Can anyone suggest a fix to this?

Sub CreateWordDoc()
Dim BenefitRow, BenefitCol, LastRow As Long
Dim TagName, TagValue, Filename As String
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim WordContent As Word.Range
On Error Resume Next
With Sheets("VBA Output")


Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True 'Make visible



LastRow = .Range("A9999").End(xlUp).Row 'Determine last row
For BenefitRow = 4 To 6
Set WordDoc = WordApp.Documents.Open(Filename:=" template name.dotm", ReadOnly:=False) 'Open Template saved as .dotm
For BenefitCol = 1 To 79
TagName = .Cells(3, BenefitCol).Value 'Tag Name
TagValue = .Cells(BenefitRow, BenefitCol).Value 'Tag Value
With WordDoc.Content.Find
.Text = TagName
.Replacement.Text = TagValue
.Wrap = wdFindContinue
.Execute Replace:=wdReplaceAll ',Forward:True, Wrap:wdFindContinue

End With
Next BenefitCol

Filename = ThisWorkbookPath & "\" & .Range("E" & BenefitRow).Value & ".docx"
WordDoc.SaveAs Filename
WordDoc.Close

Next BenefitRow
End With
WordApp.Quit

End Sub

Your code contains elementary mistakes typical of someone who has dived into trying to use VBA without first working through a tutorial. BenefitRow and BenefitCol are variants not Longs. TagName and TagValue are variants not strings. Set WordApp = CreateObject("Word.Application") should be Set WordApp = New WordApplication rather than using late bound object. Set WordDoc = WordApp.Documents.Open should be WordApp.Documents.Add to create a new document based on a template. You use of open means you are changing the template itself. — freeflow
Put option explicit at the top of your code module. Install the fantabulous RubbeDuck VBA addin and pay attention to the code inspections. — freeflow

Cindy Meister Cindy Meister · Accepted Answer · 2019-05-09T17:32:18

The problem (error message) you're seeing comes from opening a template file then wanting to save it as a "plain vanilla" document. This isn't how Word was designed to be used, which is why Word is basically saying, "Are you sure that's what you want to do?"

A template should not be opened unless the purpose is to change the template, itself. In that case, it would be saved again as a template - no message would be displayed.

When creating new documents from a template use the Documents.Add method:

Set WordDoc = WordApp.Documents.Add(Template:=" template name.dotm")

This automatically creates a copy of the template - there's no danger of overwriting the template. And the message mentioned in the question will not appear when the SaveAs method is executed.

Excel to Word VBA Export - Word Documents Not Being Created

1 Answers