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

 

[Macropod]

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

There is nothing in the code you posted to update any bookmarks. Plus you shouldn't be using:
Set wddoc = WdApp.Documents(strDocName)
or:
Set wddoc = WdApp.Documents.Open(strDocName)
and there is no need to activate the Word application. Finally, if your Run "OP" code is supposed to do the bookmark updating, you'd normally do that before killing of the wddoc & WdApp references. Try:

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 & 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)
    Run "OP"
'Release system memory that was reserved for the two Object variables.
  Set wdDoc = Nothing: Set WdApp = Nothing
End Sub

 

[Desmondo]

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

Thanks macropod I have tried something similar. I am aware my code has nothing to update bookmarks as I ended up removing it as it wasn't working. The run op actually runs a similar macro that pulls up the other word template that I need to populate with the same bookmarks as wasn't sure if I could do it in one macro both were assigned to a print /save command button but thought it would be easier to call one at a time. Cant mind exactly what I wrote as not at home just now. But will provide an example when its available.
So if i understand properly I need to declare all bookmarks as separate variables.
Something like
Dim bmk As bookmark
Bmk(bookmarkname)=textboxt1.value

And so on?

Thanks in advance

 

[Macropod]

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

How you would update the bookmark depends on whether you might need to overwrite whatever you've output to. If you don't need to do that, you could use code like:

    Set wdDoc = WdApp.Documents.Add(strDocName)
    With wdDoc
      .Bookmarks("Bookmark1").Range.Text = textboxt1.Value
      .Bookmarks("Bookmark2").Range.Text = textboxt2.Value
    End With

 

[Desmondo]

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

Thanks again that was super quick help. The created letters will not be overwritten as will be unique and in there own directory using a unique ref from userform which is for another day ?. The original template should hopefully retain the original bookmarks. I will have a tinker and see how I get on. Thanks again

 

[Desmondo]

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

Woohoo its now updating the template. I have another small problem hope you can help on that to. I can only use each bookmark once in word so I cannot replicate the information at other parts of the template. I have tried cross referencing but word wont set the link to the empty bookmark. Is there a work around.? Really appreciate your help on this no end.

 

[Macropod]

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

 

[Desmondo]

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

Thanks again got round passed that bit by just simply just adding like name1,name2 ,title 1as bookmarks etc and linking to original form control and works perfect. Now to work on setting a prompt to print and create a directory for each client. Using mkdir i think to save to. Should be fun. Just so pleased its working now. Couldn't have got there without your help.

 

[Desmondo]

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

I wonder if you could give me your thoughts on this idea. I have all my forms at present linking to separate buttons. I would like to make it just one button, can I call each sub from within one. End game is to have one button which does the print save function. So in the end when this button is pressed it checks an archive to see if a directory is there if not it creates it based on clients name and Ref and saves the form/s there. So to summarize the button is pressed a directory is checked for and created if necessary. All forms are presented with a message box stating would I like to save and print then saves all to created directory. My thought were to create a sub and call it when button is pressed which make the dir, then brings up the letters/forms asks do I wish to save later in the script. Before closing all docs.

 

[Macropod]

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

If that's what you want to do, I don't see it as being a particular problem. I don't understand what you mean by "Before closing all docs", though, as the code discussed in this thread only ever deal with one document.