Microsoft Office Forums - View Single Post - [Solved] Help needed in excel VBA to transfer bookmarks

gmayor · 11-08-2014, 11:19 PM

It is not clear why the userform is in Excel but the results go to Word, but no matter. That can be done (even if I personally would put the userform in Word and read any data from Excel into it).

Below is a simple function that will put the results in a Word document bookmarked location. (You might also consider using DocVariables and their associated fields and/or writing directly to Word ranges, but for this example, we'll stick with the bookmarks.

Presumably your useform will have an 'OK' button and a 'Cancel' button? Code them as follows:

Code:

Private Sub CommandButton1_Click() 'Continue/OK button
    Me.Hide
    Me.Tag = 1
End Sub

Private Sub CommandButton2_Click() 'Cancel Button
    Me.Hide
    Me.Tag = 0
End Sub

The following macros (in a normal Excel VBA module) will call the userform and process the results, putting the text from the userform text boxes into the Word bookmarks. If you run the form again the bookmarked values will change to reflect the new values. Put any code to interface with Excel in the main macro below and not in the userform Change the names of the userform, fields and buttons, document and bookmark names to reflect what you have:

Code:

Sub WriteToWord()
Dim wdApp As Object
Dim wdDoc As Object
Dim oForm As New UserForm1        'Userform name
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    Set wdDoc = wdApp.Documents.Open("C:\Path\DocumentName.docx")
    On Error GoTo lbl_Exit
    With oForm
        .Show
        If .Tag = 0 Then GoTo lbl_Exit
        FillBM wdDoc, "Bookmark1", .TextBox1.Text
        FillBM wdDoc, "Bookmark2", .TextBox2.Text
        'etc
    End With
lbl_Exit:
    Unload oForm
    Set wdApp = Nothing
    Set wdDoc = Nothing
    Set oForm = Nothing
    Exit Sub
End Sub


Public Sub FillBM(oDoc As Object, strBMName As String, strValue As String)
Dim oRng As Object
    With oDoc
        On Error GoTo lbl_Exit
        Set oRng = .Bookmarks(strBMName).Range
        oRng.Text = strValue
        oRng.Bookmarks.Add strBMName
    End With
lbl_Exit:
    Exit Sub
End Sub