Renaming Word Document from Excel VBA

[ravience]

Hey all!


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. On top of this (and I am not sure if this is even possible) I wish to password encrypt the document.


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


How would I go about this?
Many thanks in advance.

[ravience]

I've managed to get the item to save by setting it as an Object and then saving that, see code below, but I am not sure on how to use the name pulled from a cell nor how to password encrypt the file.


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:="/Users/Aafrika/Desktop/Output.docx", FileFormat:=12 'wdFormatXMLDocument
DoEvents
End With


End Sub

[snb]

Replace your code by:


Sub M_snb()
sn = ActiveSheet.Range("C1:C2")

With GetObject("G:\OF\Users\Aafrika\Desktop\Test.docx")
With .Content.Find
.clearformatting
.Execute "<date>", , , , , , , , , Format(sn(j, 1), "dd/mm/yyyy"), 2
.Execute "<amount>", , , , , , , , , Formatcurrency(sn(j, 2)), 2
End With
.Close -1
End With
End Sub

[Kenneth Hobs]

Like Excel, you can record a macro in Word to get syntax for some application commands. e.g.

ActiveDocument.SaveAs2 FileName:="ken2.docx", FileFormat:= _ wdFormatXMLDocument, LockComments:=False, Password:="ken", AddToRecentFiles _
:=True, WritePassword:="ken", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False, CompatibilityMode:=15

I am not sure what the Renaming in the subject line means. If you actually meant that, with the document closed, VBA's Name file1 As file2 will rename a file.