Consulting

Results 1 to 4 of 4

Thread: Renaming Word Document from Excel VBA

  1. #1
    VBAX Newbie
    Joined
    Aug 2019
    Posts
    2
    Location

    Renaming Word Document from Excel VBA

    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.





  2. #2
    VBAX Newbie
    Joined
    Aug 2019
    Posts
    2
    Location
    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

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,522
    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

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,852
    Location
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •