Need Excel Vba code to open Just Saved Word doc Hyperlink ? or Document.Open? or Other Solution

[Joyner]

Hello I have the below code that works, but need to add a part to open the word document saved using a name defined in the code. Please note that the code may not be set up correctly since I am certainly not an expert and have tried to adapted code I have found here.

I have an Excel file with an embedded Word document I use as a template. The code successfully opens the document (Object on a sheet) and updates fields then does a Saveas to the current Excel file path and my defined name. However, the problem is the word file that remains open at that point, is not the file saved to the path, but is still the object from the
Excel file. So if the user makes changes and then just “saves”, they are saving the changes to the Excel Word object, not the document saved to the file path/folder.

I have not been able to find a workable solution in my searches, so my though has been to close the Word object, and simply reopen the document just saved. But for some reason I can’t seem to do that. I though a simply open hyperlink to the file name just created and saved would work, or open the just saved file, but I can’t seem to get an of those to work.

I have tried all of the coded out lines at the bottom. I don’t know why these don’t work.

Any help to get these to work, or if you have a better solution I would really appreciate it.

Thank you

Sub OpenMemo()

Dim wordApp As Word.Application
Dim objWord As Object
Dim objWord2 As Word.Document
Dim ws As Worksheet
Dim myFileName As String
Dim strName_1 As String
'     

‘Open Word app if not open
On Error Resume Next
Set wordApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wordApp = CreateObject("Word.Application")
End If
On Error GoTo 0
'     
wordApp.Visible = True
'     
Set ws = ThisWorkbook.Sheets("Setup")
'     
strName_1 = ws.Range("Name_1").Value
    myFileName = ThisWorkbook.Path & "\" & "Memo " & strName_1
    '     
Set objWord = ThisWorkbook.Sheets("Setup").OLEObjects("Memo")
    objWord.Verb Verb:=xlPrimary
   '      
  objWord.Visible = True
    '     
Set objWord2 = ActiveDocument
'     

        objWord2.Variables("Name_1") = ws.Range("Name_1").Value
        objWord2.Variables("ADDR_1") = ws.Range("ADDR_1").Value
        objWord2.Variables("ADDR_2") = ws.Range("ADDR_2").Value
        objWord2.Variables("CITY") = ws.Range("CITY").Value
        objWord2.Variables("STATE") = ws.Range("STATE").Value
        objWord2.Variables("ZIP") = ws.Range("ZIP").Value
        
        objWord2.Range.Fields.Update
        
'        
objWord2.SaveAs2 Filename:=myFileName  '     This save works, do I would think I could just open this saved file somehow....
'     

'     I have tried the below lines of code'   
  '     
'Application.FollowHyperlink myFileName, True
'ActiveWorkbook.FollowHyperlink myFileName, True
'ActiveWorkbook.FollowHyperlink Address:=myFileName 
'two different errors for this line, somethings - Filename or class name not found during Automation operation (Error 432), and something “Cannot open the specified file”

'Set objWord2 = CreateObject(myFileName)
'objWord2.Visible = True

'Documents.Open myFileName

 'objWord2.Close
 'Set objWord2 = Documents.Open(Filename:=myFileName)
 'objWord2.Visible = True
'
    
  Set wordApp = Nothing
  Set objWord = Nothing
  Set objWord2 = Nothing
    
End Sub