Transferring Input on excel userform to word template via bookmarks

[Desmondo]

I have set up a nice userform for some work related stuff, but for the last few weeks i have been losing hair rapidly trying to figure how to transfer data between the excel userform and my word templates. Most articles on the web suggest using word bookmarks which i have attempted but always get errors. I have all the ms office libraries references etc checked.

I have labelled all my word templates at the appropriate area with an appropriate bookmark. I have labelled all form controls in excel userform with the same and assigned the code to a command button.

I am able to open the documents just fine from excel but nothing transfer moves, i have had various errors in all my attempts. The end goal is that i open the correct template and populate the bookmarks, make a copy and save to another directory in windows. I have two userform one is a menu that launches other userforms depending on selection all userform share some of the same information like name, address, ref, etc etc with the exception of form 1 which is just a menu.

Can anyone provide me an easy to follow example this would be very much appreciated.

The code i have so far which opens the docs

Private Sub PSave_Click()
'Declare obj variables for the word application and document.
      Dim WdApp As Object, wddoc As Object
'Declare a String variable for the example document's name and folder path.
     Dim strDocName As String
'On Error statement if Word is not already open.
On Error Resume Next
     'Activate Word if it is already open.
     Set WdApp = GetObject(, "Word.Application")
     If Err.Number = 429 Then
     Err.Clear
'Create a Word application if Word is not already open.
     Set WdApp = CreateObject("Word.Application")
     End If
'Make sure the Word application is visible.
    WdApp.Visible = True
'Define the strDocName String variable.
   strDocName = "C:\Users\desmo\Desktop\New folder (5)\Work docs\UC372.dotx"
'Check the directory for the presence of the document
'name in the folder path.
'If it is not recognized, inform the user and exit the macro.
    If Dir(strDocName) = "" Then
    MsgBox "The file UC372" & vbCrLf & _
    "was not found in the folder path" & vbCrLf & _
    "C:\Users\desmo\Desktop\New folder (5)\Work docs\UC372.dotx", _
    vbExclamation, _
   "Sorry, that document name does not exist."
Exit Sub
End If
'Activate the Word application.
    WdApp.Activate
'Set the Object variable for the Word document's full name and folder path.
    Set wddoc = WdApp.Documents(strDocName)
'If the Word document is not already open, then open it.
   If wddoc Is Nothing Then Set wddoc = WdApp.Documents.Open(strDocName)
'The document is open, so activate it.
   wddoc.Activate
'Release system memory that was reserved for the two Object variables.
  Set wddoc = Nothing
  Set WdApp = Nothing
  Run "OP"
End Sub

 

[Desmondo]

As indicated in post #25 , you would use:
Call PasteBookMark(wdDoc, "BreakDown")

Still not hitting the bookmark, selecting the range ok and copy to clipboard but not pasting to destination bookmark "BreakDown".

 

[Macropod]

Try doing the copy immediately before that line. You current code has a lot going on between the existing copy code and the call the that macro.

 

[Desmondo]

Try doing the copy immediately before that line. You current code has a lot going on between the existing copy code and the call the that macro.

Tried as stated and tried after the update word fields. But still no joy. I will try again tomorrow.

Thanks for all your patience and help.

 

[Macropod]

Create a test document with some boilerplate text and a bookmark named 'BkMk'. Add the following VBA code to it:

Sub Test()
Dim wdDoc As Document
Call PasteBookMark(ActiveDocument, "BkMk")
End Sub

Sub PasteBookMark(wdDoc As Object, BkMkNm As String)
Dim BmkRng As Range
With wdDoc
  If .Bookmarks.Exists(BkMkNm) Then
    Set BmkRng = .Bookmarks(BkMkNm).Range
    With BmkRng
        .Delete
        .Paste
    End With
    .Bookmarks.Add BkMkNm, BmkRng
  End If
End With
Set BmkRng = Nothing
End Sub

Open an Excel workbook and copy something to the clipboard, then run the 'Test' macro.