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]

Re: Transposing Input on excel userform to word template via bookmarks

What I mean is I have duplicated the code for other forms / letter which feed of the input from the user form. At present I have a button for each form / letter and it pre populates each at the required bookmark. But want just one button so want to call each form and do the save and create directory thing.

 

[Macropod]

Re: Transposing Input on excel userform to word template via bookmarks

You can have separate buttons with minimal code duplication if the code is properly structured so that each calls shared subs/functions at the appropriate points. For example, the 'UpdateBookmark' sub I posted can be called by any sub that passes the appropriate strings to it.

 

[Desmondo]

Re: Transposing Input on excel userform to word template via bookmarks

I struggled with understanding that bit of code, I ideally I only want the one button that calls all modules for the forms / letters. Just really a print save button. That allows a preview of the letters forms and offers a preview of each before saving to the archive. Big ask but my thinking was modular for each function and call from main script.

 

[Macropod]

Re: Transposing Input on excel userform to word template via bookmarks

Well, without seeing the rest of your code, no-one can be expected to give specific advice.

 

[Desmondo]

Re: Transposing Input on excel userform to word template via bookmarks

Macropod you have been immense with your help. I cannot get over your help and pointers. I am not from an ms background but can do js etc. I have to be careful what I post as sensitive data gvmt. My thinking as I have said is to create functions / modules that do whats necessary like check for directory if not there create, populate forms and then save. Most of whats necessary is is working now thanks to your help. I will post my code on Monday when back at work. I reiterate your help has been amazing and if you have a donate site I will happily do so. Really just learning vba and it doesn't seem totally different to what i have done in the past. And you have been like a mentor thanks.

 

[Desmondo]

Re: Transposing Input on excel userform to word template via bookmarks

Is it possible to update another workbook not embedded in the current workbook based on the same userform entries? Cannot fathom the syntax for the life of me .

 

[Macropod]

Re: Transposing Input on excel userform to word template via bookmarks

Yes, but that's an entirely different matter, so you should post a new question in the Excel forum about that.

 

[Desmondo]

Re: Transposing Input on excel userform to word template via bookmarks

OK thanks

 

[Desmondo]

Re: Transposing Input on excel userform to word template via bookmarks

In that case, try:

'Declare obj variables for the word application and document.
    Dim WdApp As Object, wdDoc As Object, wdRng As Object

Private Sub PSave_Click()
'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 & strDocName, _
    vbExclamation, _
   "Sorry, that document name does not exist."
  Exit Sub
End If
'Create the Word document from the template.
    Set wdDoc = WdApp.Documents.Add(strDocName)
'Populate the Word document's bookmarks.
    Call UpdateBookmark("Bookmark1", textboxt1.Value)
    Call UpdateBookmark("Bookmark2", textboxt2.Value)
'Update the Word document's cross-references.
    wdDoc.Fields.Update
'Release system memory that was reserved for the Object variables.
  Set wdRng = Nothing: Set wdDoc = Nothing: Set WdApp = Nothing
End Sub

Sub UpdateBookmark(strBkMk As String, strTxt As String)
With wdDoc
  If .Bookmarks.Exists(strBkMk) Then
    Set wdRng = .Bookmarks(strBkMk).Range
    wdRng.Text = strTxt
    .Bookmarks.Add strBkMk, wdRng
  End If
End With
End Sub

Hi Macropod now that my bookmark list is becoming huge i have been trying to get this to work. Not sure if I am implementing the solution correctly in word. What I have been doing is adding a bookmark then a field ref to the empty bookmark. This seems to be the only way to add a cross ref to an empty bookmark. I have viewed numerous guides online but word does not allow me to add a straight cross ref to an named but empty bookmark. Your wisdom on this would be much appreciated as this sounds like the way to go for maintenance. Thanks

 

[Macropod]

Re: Transposing Input on excel userform to word template via bookmarks

With the code you're using, there's no reason for the destination bookmark to be empty from the outset; indeed, having something there makes it easier to locate both the bookmark and the cross-reference.

Regardless, you can insert an empty bookmark, via Insert|Bookmark, then a cross-reference to it via Insert|Cross-reference. Toggling the field code display on/off via Alt-F9 will show that your cross-references are being inserted.