Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA to open word template, populate, then save as .docx file somewhere else

Tags:

excel

vba

I created a word template with placeholders such as <> that I am then able to replace automatically with my excel macro. When I tried this process again, the word document now opens saying it is a read only document. How am I supposed to save my Word Template so it can be edited? Also, when I open the word template through my excel macro, how does it know to save it as a new word document, and not save it as an updated template?

Here is my code:

Sub ReplaceText()
Dim wApp As Word.Application
Dim wDoc As Word.Document
Set wApp = CreateObject("Word.Application")
wApp.Visible = True

Set wDoc = wApp.Documents.Open("file name here")

With wDoc
    .Application.Selection.Find.Text = "<<name>>"
    .Application.Selection.Find.Execute
    .Application.Selection = Range("A5")
    .Application.Selection.EndOf

    .Application.Selection.Find.Text = "<<dob>>"
    .Application.Selection.Find.Execute
    .Application.Selection = Range("A6")

    .SaveAs2 Filename:=("file name goes here"), _
    FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False
End With

End Sub
like image 893
Brian Avatar asked Sep 25 '14 15:09

Brian


2 Answers

While @wahwahwah's approach works, it is still opening the template as a document for editing, then saving it in another format, while suppressing alerts. What I suspect you want to achieve is the behaviour when opening a template from the shell, which generates a "new" document based on the template. You can achieve this with the "Add" method thus;

Set wDoc = wApp.Documents.Add(Template:="file path here", NewTemplate:=False, DocumentType:=0)
like image 187
Valiante Avatar answered Oct 16 '22 10:10

Valiante


If you indicate that the file is ReadOnly while setting the file name, and you turn off alerts, this should solve the issue of the prompt:

Set wApp = CreateObject("Word.Application")
wApp.DisplayAlerts = False
Set wDoc = wApp.Documents.Open Filename:="C:\Documents\SomeWordTemplate.dot", ReadOnly:=True

And when you go to save the file, just save it with the ".doc" file extension instead of ".dot" so its saved as a word file type. You can also change the file name and output directory path if you so choose. (Also, remember to turn the alerts back on)

With wDoc

.ActiveDocument.SaveAs Filename:="C:\Documents\NewWordDocumentFromTemplate.doc"

End With

wApp.DisplayAlerts = True

Hope this helps!

like image 33
wahwahwah Avatar answered Oct 16 '22 12:10

wahwahwah