How do I change text in a Word Doc from Excel VBA when Word Doc is located on a server?

Question

I have a macro that creates an experiment report based on a word template and then saves it to a remote server. A cell is selected in my Excel worksheet, the macro runs, an instance of Word opens with a blank template, and the file is saved based on the selected cell and other data. What I want to do is edit the Heading in the blank template and update it to the name of the experiment which is located in the first column of the row of the selected cell. The code below works when I open a file from my local files but does not work when the file is located on the server. ``` Dim oWord As Object Set oWord = CreateObject("Word.Application") oWord.Visible = True oWord.Activate ' Open a new instance of the ExperimentTemplate and save it oWord.Documents.Add Template:="ExperimentTemplate", NewTemplate:=False, DocumentType:=0 oWord.ActiveDocument.SaveAs FileName:=fPath & "example.docx", FileFormat:=wdFormatXMLDocument 'fPath is the path to the folder where file will be saved on the server oWor…

Accepted Answer

Can you try this code for me?

```
Dim oWord As Object
Dim oSelection As Object
Dim sel As Object

Set oWord = CreateObject("Word.Application")
oWord.Visible = True
oWord.Activate

oWord.Documents.Add Template:="ExperimentTemplate", NewTemplate:=False, DocumentType:=0
oWord.ActiveDocument.SaveAs FileName:=fPath & "example.docx", FileFormat:=wdFormatXMLDocument 'fPath is the path to the folder where file will be saved on the server
oWord.ActiveDocument.Close

' Re-open file and change Experiment Name
oWord.Documents.Open FileName:=fPath  & "example.docx"

Set oSelection = oWord.Documents(1).Content

oSelection.Select

Set sel = oWord.Selection

With sel
    .Find.ClearFormatting
    .Find.Replacement.ClearFormatting
    With .Find
        .Text = "Experiment Name"
        .Replacement.Text = Name 'Hope you have declared it somewhere?
        .Forward = True
        .Wrap = 1 'wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        sel.Find.Execute Replace:=2  'wdReplaceAll
   End With
End With
```

How do I change text in a Word Doc from Excel VBA when Word Doc is located on a server?

Solution

Problem