ravience
08-23-2019, 09:59 PM
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.
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.