Using Excel to Save a Word File With Name

Question

I have a Word template which I am using with < amount >, < account > etc. which I am then having a VBA script in Excel pull data from cells and replace the < amount > etc. with the value in the cell.

I've got it working fantastically now but I am having some troubles with saving the Word document. Essentially I am wanting to have the Excel script pull the name for the document from a cell and then save the document with that as its name in a different location as to not save over the template.

Essentially my goal is to fill data into a handful of cells and then trigger a VBA script which replaces text on the Word document template and then saves the document with a particular name.

I've attempted the answers from a similar question here Excel VBA to open word template, populate, then save as .docx file somewhere else but it isn't working within my code.

Here's the existing code so far:

Option Explicit

Public Sub WordFindAndReplace()
Dim ws As Worksheet, msWord As Object

Set ws = ActiveSheet
Set msWord = CreateObject("Word.Application")

With msWord
.Visible = True
.Documents.Open "/Users/Aafrika/Desktop/Test.docx"
.Activate

With .ActiveDocument.Content.Find
    .ClearFormatting
    .Replacement.ClearFormatting

    .Text = "<date>"
    .Replacement.Text = Format(ws.Range("C1").Value2, "dd/mm/yyyy")

    .Forward = True
    .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False

    .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)

    .Text = "<amount>"
    .Replacement.Text = Format(ws.Range("C2").Value2, "currency")

    .Forward = True
    .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False

    .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)


End With
.Quit SaveChanges:=True
End With
End Sub

Any ideas on how to get this to work would be great. Thanks in advance!

.Quit SaveChanges:=True You are overwriting the main document. You have to use .SaveAs. Record a macro in Word and you will get the code for .SaveAs — Siddharth Rout
– Siddharth Rout, Commented Aug 24, 2019 at 4:41
@SiddharthRout I did try this, but it saves the Excel document, not the Word document — numpty
– numpty, Commented Aug 24, 2019 at 4:42
Declare a word document object and work with that. Dim oDoc As Object: Set oDoc = msWord.Documents.Open("/Users/Aafrika/Desktop/Test.docx") and then use oDoc.SaveAs — Siddharth Rout
– Siddharth Rout, Commented Aug 24, 2019 at 4:48
I have posted an example. You may have to refresh the page to see it — Siddharth Rout
– Siddharth Rout, Commented Aug 24, 2019 at 4:57

Siddharth Rout · Accepted Answer · 2019-08-24 04:57:02Z

3

Work with objects. it will make your life very easy. You are saving and closing the original document. See this example. This creates relevant objects and then works with it.

Is this what you are trying? (untested)

Option Explicit

Private Sub WordFindAndReplace()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim msWord As Object
    Dim msWordDoc As Object
    Set msWord = CreateObject("Word.Application")

    msWord.Visible = True
    Set msWordDoc = msWord.Documents.Open("/Users/Aafrika/Desktop/Test.docx")

    With msWordDoc
        With .Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting

            .Text = "<date>"
            .Replacement.Text = Format(ws.Range("C1").Value2, "dd/mm/yyyy")

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)

            .Text = "<amount>"
            .Replacement.Text = Format(ws.Range("C2").Value2, "currency")

            .Forward = True
            .Wrap = 1               'wdFindContinue (WdFindWrap Enumeration)
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
        End With
        .SaveAs Filename:="Some File Name", FileFormat:=12 'wdFormatXMLDocument
        DoEvents
        .Close (False)
    End With

    msWord.Quit
End Sub

answered Aug 24, 2019 at 4:57

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

numpty Over a year ago

Hmm, it seems very promising but I'm now getting the popup "Office is still updating linked or embedded objects for this workbook." and the file isn't getting created.

Siddharth Rout Over a year ago

.SaveAs Filename:="Some File Name", FileFormat:=12 'wdFormatXMLDocument What did you type in "Some File Name"

Siddharth Rout Over a year ago

"Office is still updating linked or embedded objects for this workbook." That is an Excel message. Seems like you have some embedded objects in Excel.

numpty Over a year ago

I tried it just with "output" to no avail. What would you suggest to fix that error, I am not too familiar with all this so I apologise.

numpty Over a year ago

I figured the password bit out! You've got me excited to delve more into this stuff, thank-you @Siddharth

|

Collectives™ on Stack Overflow

Using Excel to Save a Word File With Name

1 Answer 1

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related