Problem entering text into Word document from Excel macro

[Nonei]

Hi folks,

I have an Excel macro that checks for the existence of a Word document, opens it if it exists and opens a generic document if not and then saves that generic document with a name and ID number as the file name. That part is working fine.

The problem is that I would like it to enter that name and ID number into a bookmark spot in the Word document and it keeps erroring out. I have found a few codes on this in the forums but they don't seem to fix the issue. I know it has to be something simple wrong with my coding.

Edit: the error was something about the object... sorry, can't remember... when I went to test it just now it's giving me that the bookmark doesn't exist. I did double check spelling and capitalization of the bookmark and it matches MbrNamePME that is in the macro. Thanks

Here is the relevant code - it errors at the .Goto What line

If Filename = "zzzexample" Then
    Filename = Targmem.Value & " " & Targmem.Offset(0, 5)
    Objdoc.SaveAs (Filepath & Filename & ".docx")
    With objWord.activedocument
        .Goto What:=wdGoToBookmark, Name:="MbrNamePME"
        .TypeText Filename
    End With
End If

And the full code, in case it helps

Sub Open_Word_Document()

On Error GoTo 0
Dim x As Integer
Dim objWord As Object
Dim Objdoc As Object
Dim Filepath As String, Filename As String
Dim Targmem As Range
Dim bkmrksize As Integer

Set Targmem = Selection.Resize(1, 1)
x = 0
If Intersect(Targmem, Range("MbrName")) Is Nothing Or IsEmpty(Targmem) Then
    MsgBox prompt:="Please select a member from the lefthand column"
    Exit Sub
End If
 
'Change the directory path and file name to the location
'of your document
Filepath = Left(ActiveWorkbook.Path, InStrRev(ActiveWorkbook.Path, "\")) & "Notes\"
Filename = Targmem.Value & " " & Targmem.Offset(0, 5)

'MsgBox prompt:=Filepath & Filename
    Dim f As Boolean
    On Error Resume Next
    Set Objdoc = GetObject(Filepath & Filename & ".docx")
    If Objdoc Is Nothing Then
        Set objWord = GetObject(, "Word.Application")
        If objWord Is Nothing Then
            Set objWord = CreateObject("Word.Application")
            If objWord Is Nothing Then
                MsgBox "Failed to start Word!", vbCritical
                Exit Sub
            End If
            f = True
        End If
        
        On Error GoTo zzzexample
        Set Objdoc = objWord.Documents.Open(Filepath & Filename & ".docx")
        If x > 2 Then
            MsgBox prompt:="x is " & x
            Exit Sub
        End If
        objWord.Visible = True
        On Error GoTo 0
        
        If Objdoc Is Nothing Then
            MsgBox "Failed to open document.", vbCritical
            If f Then
                objWord.Quit
            End If
            Exit Sub
        End If
        objWord.Visible = True
        objWord.Activate
    Else
        With Objdoc.Parent
            .Visible = True
            .Activate
        End With
    End If

If x > 2 Then
    MsgBox prompt:="x is " & x
    Exit Sub
End If
objWord.Visible = True
On Error GoTo 0
'Saveas with member name
If Filename = "zzzexample" Then
    Filename = Targmem.Value & " " & Targmem.Offset(0, 5)
    Objdoc.SaveAs (Filepath & Filename & ".docx")
    With objWord.activedocument
        .Goto What:=wdGoToBookmark, Name:="MbrNamePME"
        .TypeText Filename
    End With
End If

Exit Sub
zzzexample:
x = x + 1
Select Case x
    Case 1
        Filename = Left(Targmem.Value, InStrRev(Targmem.Value, " ") - 1) & " " & Targmem.Offset(0, 5)
    Case 2
        Filename = "zzzexample"
    Case Else
        On Error GoTo 0
End Select
'MsgBox prompt:=Filepath & Filename
Resume

End Sub

Thank you!!

 

[NdNoviceHlp]

Your code seems somewhat confusing. Maybe trial this untested simplified code. There is an exists property for bookmarks that can be used to see if the bookmark is present. HTH. Dave

Sub Open_Word_Document()

On Error GoTo 0
Dim x As Integer
Dim objWord As Object
Dim Objdoc As Object
Dim Filepath As String, Filename As String
Dim Targmem As Range
Dim bkmrksize As Integer

Set Targmem = Selection.Resize(1, 1)
x = 0
If Intersect(Targmem, Range("MbrName")) Is Nothing Or IsEmpty(Targmem) Then
MsgBox prompt:="Please select a member from the lefthand column"
Exit Sub
End If

'Change the directory path and file name to the location
'of your document
Filepath = Left(ActiveWorkbook.Path, InStrRev(ActiveWorkbook.Path, "\")) & "Notes\"
Filename = Targmem.Value & " " & Targmem.Offset(0, 5)

Set objWord = GetObject(, "Word.Application")
If objWord Is Nothing Then
Set objWord = CreateObject("Word.Application")
If objWord Is Nothing Then
MsgBox "Failed to start Word!", vbCritical
GoTo ErFix
End If
End If

On Error GoTo ErFix:
Set Objdoc = objWord.Documents.Open(Filepath & Filename & ".docx")
objWord.Visible = True
On Error GoTo 0
If Filename = "zzzexample" Then
Filename = Targmem.Value & " " & Targmem.Offset(0, 5)
End If
With Objdoc
.Goto What:=wdGoToBookmark, Name:="MbrNamePME"
.TypeText Filename
End With
Objdoc.SaveAs (Filepath & Filename & ".docx")

Exit Sub
ErFix:
Set Objdoc = Nothing
Set objWord = Nothing
End Sub

 

[NdNoviceHlp]

This is probably better... again untested. Dave

Sub Open_Word_Document()
Dim x As Integer
Dim objWord As Object
Dim Objdoc As Object
Dim Filepath As String, Filename As String
Dim Targmem As Range
Dim bkmrksize As Integer

Set Targmem = Selection.Resize(1, 1)
If Intersect(Targmem, Range("MbrName")) Is Nothing Or IsEmpty(Targmem) Then
MsgBox prompt:="Please select a member from the lefthand column"
Exit Sub
End If

'Change the directory path and file name to the location
'of your document
Filepath = Left(ActiveWorkbook.Path, InStrRev(ActiveWorkbook.Path, "\")) & "Notes\"
Filename = Targmem.Value & " " & Targmem.Offset(0, 5)
If Filename = "zzzexample" Then
Filename = Targmem.Value & " " & Targmem.Offset(0, 5)
End If

Set objWord = GetObject(, "Word.Application")
If objWord Is Nothing Then
Set objWord = CreateObject("Word.Application")
If objWord Is Nothing Then
MsgBox "Failed to start Word!", vbCritical
GoTo ErFix
End If
End If

On Error GoTo ErFix:
Set Objdoc = objWord.Documents.Open(Filepath & Filename & ".docx")
On Error GoTo 0
With Objdoc
If .Bookmarks.Exists("MbrNamePME") = True Then
.Goto What:=wdGoToBookmark, Name:="MbrNamePME"
.TypeText Filename
Else
MsgBox "No bookmark"
GoTo ErFix
End If
End With
Objdoc.SaveAs (Filepath & Filename & ".docx")
objWord.Visible = True
Exit Sub

ErFix:
MsgBox "File error " & Filepath & Filename & ".docx"
Set Objdoc = Nothing
objWord.Quit
Set objWord = Nothing
End Sub