Saving a Word Document from Excel

[Nick_De]

Hello All,

I am hoping someone can help me with a problem I have been surfing the forums trying to find a solution to for a few hours now. I have an Excel macro file which will be stored in our company's SharePoint which will be used to track contracts being created and also serve a process for creating the actual contracts which are created in Word (Also stored in SharePoint).

I have managed to create the Excel file and code which opens the Word file (.docx) and populates all the relevant data but I am having an issue with saving the word file automatically with a dynamic name (SaveAs). My Code is below

    Dim ws As Worksheet, msWord As Object, itm As Range, fname As String
    fname = cName
    Set ws = ActiveSheet
    Set msWord = CreateObject("Word.Application")


    With msWord
        .Visible = True
        .Documents.Open "N:\Test\Test.docx"
        .Activate
        
        With .ActiveDocument.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting


            For Each itm In ws.UsedRange.Columns("A").Cells


                .Text = itm.Value2                          'Find all strings in col A


                .Replacement.Text = itm.Offset(, 1).Value2  'Replacements from col B


                .MatchCase = False
                .MatchWholeWord = False


                .Execute Replace:=2     'wdReplaceAll (WdReplace Enumeration)
            
            Next
        End With
        .Application.ActiveDocument.SaveAs Filename:="N:\Test\" & fname & ".docx"


    End With

This error I get is Run-time Error '4198' Command Failed

Any help would be greatly appreciated.

Nick

 

[DanteAmor]

Hi @★ Nick_De, Welcome to the forum!

The error is because the fname variable is empty, at the beginning of the code you have this:
fname = cName
But cName is empty, therefore, fname is empty.

Just fill in the fname variable with some data and try again.
eg.
fname = "new doc"