# Transferring Input on excel userform to word template via bookmarks



## Desmondo (Oct 19, 2017)

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 (Oct 19, 2017)

*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 (Oct 20, 2017)

*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 (Oct 20, 2017)

*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 (Oct 20, 2017)

*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 (Oct 20, 2017)

*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 (Oct 20, 2017)

*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 (Oct 20, 2017)

*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 (Oct 21, 2017)

*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 (Oct 21, 2017)

*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.


----------



## Desmondo (Oct 19, 2017)

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 (Oct 21, 2017)

*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 (Oct 21, 2017)

*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 (Oct 21, 2017)

*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 (Oct 21, 2017)

*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 (Oct 21, 2017)

*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 (Oct 24, 2017)

*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 (Oct 24, 2017)

*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 (Oct 24, 2017)

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

OK thanks


----------



## Desmondo (Nov 9, 2017)

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



Macropod said:


> In that case, try:
> 
> ```
> 'Declare obj variables for the word application and document.
> ...



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 (Nov 9, 2017)

*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.


----------



## Desmondo (Oct 19, 2017)

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 (Nov 9, 2017)

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

Ah got it now,  didn't think it was doing anything but alt f9 did the trick. Thanks again


----------



## Desmondo (Nov 9, 2017)

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



Macropod said:


> 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.



Hi Again and sorry for being a pest. I am trying this code but its not updating the bookmarks nor the ref's. I shifted the update bookmarks() to another module and i am calling it from there, i believe correctly. The docs is opening and saving no problem to the correct directory etc. But bookmarks etc remain unchanged.

Am i missing something??


```
'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.


    Dim strFolderName As String
    Dim strSurname As String
    Dim strNino As String
    Dim strFilePath As String
    Dim Partner As String
    Dim Clmt As String
    Dim Couple As String
    Dim aPPPath As String
    
  
'Get Surname from control
    strSurname = Me.Surname
    ' Get nino  from from control
    strNino = Me.Nino


    strFolderName = strSurname & " " & strNino
    MsgBox "Folder created in Archive"
    'strFilePath = strParent & strFolderName & "\"
    aPPPath = Application.ThisWorkbook.Path & "\" & "Archive" & "\" & strFolderName & "\"


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 = Application.ThisWorkbook.Path & "\" & "Templates" & "\" & "DupC.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 Folder Was Created successfully in Directory " & vbCrLf & _
    "was not found in the folder path" & vbCrLf & _
    "\\DFZ70406.link2.gpn.gov.uk\70805003$\OPTool 191017\op.", _
    vbExclamation, _
    "Sorry, that document name does not exist."
End If


'Create the Word document from the template.
    Set wdDoc = wdApp.Documents.Add(strDocName)
    
    'Variable values to determine if couple or not for decision completion
    
    Clmt = ComboBox1.Value & " " & Surname.Value
    Partner = ComboBox2.Value & " " & PSurname.Value
    Couple = Clmt & " & " & Partner
    
    'Populate the Word Documents Bookmarks
'


With wdDoc


    Call Module1.UpdateBookmark("OPAmount", OPAmount.Value)
    Call Module1.UpdateBookmark("ClaimDate", ClaimDate.Value)
    Call Module1.UpdateBookmark("Clmts", Clmts.Value)
    Call Module1.UpdateBookmark("CorrectAmount", CorrectAmount.Value)
    Call Module1.UpdateBookmark("CorrectDate", CorrectDate.Value)
    Call Module1.UpdateBookmark("iDate", iDate.Value)
    Call Module1.UpdateBookmark("iPayt", iPyt.Value)
    Call Module1.UpdateBookmark("opStartDate", opStartDate.Value)
    Call Module1.UpdateBookmark("opEndDate", opEndDate.Value)
  
    'Update the Word document's cross-references.
    wdDoc.Fields.Update


'    .BookMarks("Title").Range.Text = ComboBox1.Value
'    .BookMarks("Forename").Range.Text = Forename.Value
'    .BookMarks("Surname").Range.Text = Surname.Value
'    .BookMarks("Nino").Range.Text = Nino.Value
'    .BookMarks("doc").Range.Text = ClaimDate.Value
'    .BookMarks("apStart").Range.Text = apStart.Value
'    .BookMarks("apEnd").Range.Text = apEnd.Value
'    .BookMarks("iPyt").Range.Text = iPyt.Value
'    .BookMarks("opStartDate").Range.Text = opStartDate.Value
'    .BookMarks("opEndDate").Range.Text = opEndDate.Value
'    .BookMarks("Title1").Range.Text = ComboBox1.Value
'    .BookMarks("iDate").Range.Text = iDate.Value
'    .BookMarks("CorrectAmount").Range.Text = CorrectAmount.Value
'    .BookMarks("CorrectDate").Range.Text = CorrectDate.Value
'    .BookMarks("iDate1").Range.Text = iDate.Value
'    .BookMarks("iPyt1").Range.Text = iPyt.Value
'    .BookMarks("iDate2").Range.Text = iDate.Value
'    .BookMarks("Title2").Range.Text = ComboBox1.Value
'    .BookMarks("Surname1").Range.Text = Surname.Value
'    .BookMarks("Surname2").Range.Text = Surname.Value
'    .BookMarks("apStart1").Range.Text = apStart.Value
'    .BookMarks("apEnd1").Range.Text = apEnd.Value
'    .BookMarks("iPyt2").Range.Text = iPyt.Value
'    .BookMarks("Title3").Range.Text = ComboBox1.Value
'    .BookMarks("Surname3").Range.Text = Surname.Value
'    .BookMarks("AddressL1").Range.Text = AddressL1.Value
'    .BookMarks("AddressL2").Range.Text = AddressL2.Value
'    .BookMarks("AL3").Range.Text = TextBox38.Value
'    .BookMarks("PCode").Range.Text = Pcode.Value
'    .BookMarks("PTitle").Range.Text = ComboBox2.Value
'    .BookMarks("PForename").Range.Text = PForename.Value
'    .BookMarks("PSurname").Range.Text = PSurname.Value
'    .BookMarks("PNino").Range.Text = PNino.Value
'    .BookMarks("PTitle1").Range.Text = ComboBox2.Value
'    .BookMarks("PForename1").Range.Text = PForename.Value
'    .BookMarks("PSurname1").Range.Text = PSurname.Value
'
'
'    If CheckBox11.Value = True Then
'    .BookMarks("Clmts").Range.Text = Couple
'    .BookMarks("Clmts1").Range.Text = Couple
'    .BookMarks("Clmts2").Range.Text = Couple
'    .BookMarks("Clmts3").Range.Text = Couple
'
'    Else
'    .BookMarks("Clmts").Range.Text = Clmt
'    .BookMarks("Clmts1").Range.Text = Clmt
'    .BookMarks("Clmts2").Range.Text = Clmt
'    .BookMarks("Clmts3").Range.Text = Clmt
  
        
    'End If
           
         
End With
    
    
     wdDoc.SaveAs aPPPath & "OP Decision.doc"
     wdDoc.Close True
    


'Release system memory that was reserved for the two Object variables.
    Set wdRng = Nothing: Set wdDoc = Nothing:  Set wdApp = Nothing
'Close Word Doc
    Call CloseWordDocuments
'Open created Folder
'Shell "explorer.exe " & strFilePath, vbNormalFocus


Call UC372




End Sub
```


----------



## Macropod (Nov 9, 2017)

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

1. You appear to have commented out:
Dim wdApp As Object, wdDoc As Object
with the result they're now undefined. Likewise, nowhere is wdRng defined.
2. With the UpdateBookmark sub in another module, you'll need to pass wdDoc to that as part of the call, e.g.:
Call Module1.UpdateBookmark(wdDoc, "OPAmount", OPAmount.Value)
3. The UpdateBookmark likewise needs to be modified to accept the wdDoc parameter (and have wdRng defined):

```
Sub UpdateBookmark(wdDoc As Object, strBkMk As String, strTxt As String)
Dim wdRng As Object
With wdDoc
  If .Bookmarks.Exists(strBkMk) Then
    Set wdRng = .Bookmarks(strBkMk).Range
    wdRng.Text = strTxt
    .Bookmarks.Add strBkMk, wdRng
  End If
End With
Set wdRng = Nothing
End Sub
```
4. Since wdRng isn't used or defined in your useform's sub, you should delete:
Set wdRng = Nothing
from there.


----------



## Desmondo (Nov 9, 2017)

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

Your the man as always all working now except one little bit i had working the other way. 

Sometime the case i deal with is a couple and in that case a checkbox is ticked to add their details. I declared a bookmark clmts in my word template. And three variables in my macro like so 


```
Clmt = ComboBox1.Value & " " & Surname.Value    Partner = ComboBox2.Value & " " & PSurname.Value
    Couple = Clmt & " & " & Partner
```

And had an if else statement like so


```
If CheckBox11.Value = True Then'    .BookMarks("Clmts").Range.Text = Couple
'    .BookMarks("Clmts1").Range.Text = Couple
'    .BookMarks("Clmts2").Range.Text = Couple
'    .BookMarks("Clmts3").Range.Text = Couple
'
'    Else
'    .BookMarks("Clmts").Range.Text = Clmt
'    .BookMarks("Clmts1").Range.Text = Clmt
'    .BookMarks("Clmts2").Range.Text = Clmt
'    .BookMarks("Clmts3").Range.Text = Clmt
  
        
    'End If
```

But i cannot fathom how i would place this in the new code, any help greatly appreciated.


----------



## Desmondo (Nov 9, 2017)

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

Its sill works under old way for that section/

Thanks


----------



## Desmondo (Nov 9, 2017)

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

Infact doesn't only does the bookmark not the refs


----------



## Macropod (Nov 9, 2017)

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

All you need in the document is a single 'Clmts' bookmark, with cross-references to it from where you now have the Clmts1, Clmts2, & Clmts3 bookmarks.
For the code, all you then need is:

```
Clmt = ComboBox1.Value & " " & Surname.Value
If CheckBox11.Value = True Then Clmt = Clmt & " & " & ComboBox2.Value & " " & PSurname.Value 
Call Module1.UpdateBookmark(wdDoc, "Clmts", Clmt)
```


----------



## Desmondo (Nov 9, 2017)

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

Top man, all working great.


----------



## Desmondo (Nov 9, 2017)

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

Macropod I tell you what you are **** good at this stuff. Don't know what i would do without you I have read a few books on this and in the middle of an online course. But feel like I only learn when putting it into practice. Thanks for all your patience. 

I hope you don't mind me asking you one more thing, something i thought would have been simple but turns out to be far from it. Scouted every where to no avail. 

Some of my bookmarks are in a letter and are filled with the user form text boxes. I wanted to present some of them in the format dd/mmm/yyyy but do not want to use date pickers as think this slows down input. Is there away you know off for me to update the bookmarks that are dates to this format in word or from excel vba.


----------



## Macropod (Nov 9, 2017)

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

Simply include the required format as part of the call to UpdateBookmark. For example:
Call Module1.UpdateBookmark(wdDoc, "OPDate", Format(OPDate.Value, "dd/mmm/yyyy"))


----------



## Desmondo (Oct 19, 2017)

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 (Nov 9, 2017)

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

Wow you are amazing thanks


----------



## Desmondo (Feb 7, 2018)

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

Hi Macropod, i know this is late on but i would like to try and progress this a little further to include data a from a range in a spreadsheet and insert that into a bookmark, have been playing a bit with the code you provided me with but cannot seem to crack this part. The range is created on a submit of userForm and is dynamic in that each submission can be in varying length but always column A:C in the same workbook in a sheet called "Store". The bookmark is called "Breakdown", no matter what i try it does not work and no error.


```
Sub uc371()

'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.
    Dim answer As Variant
    Dim strFolderName As String
    Dim strSurname As String
    Dim strNino As String
    Dim strFilePath As String
    Dim Partner As String
    Dim Clmt As String
    Dim Couple As String
    Dim aPPPath As String
    Dim wb As Workbook ' Latest
    Dim ws As Worksheet 'Latest
    Dim rng As Range 'Latest
    
        


'Get Surname from control
    strSurname = Me.Surname
    ' Get nino  from from control
    strNino = Me.Nino


    strFolderName = strSurname & " " & strNino
    
    strFilePath = Application.ThisWorkbook.Path & "\" & "Archive" & "\" & strFolderName & "\"
    CreateObject("WScript.Shell").PopUp "Creating your UC371!", 1
           


    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 = Application.ThisWorkbook.Path & "\" & "Templates" & "\" & "UC371.dotx"


'End If


'Create the Word document from the template.
Set wdDoc = wdApp.Documents.Add(strDocName)
Set wb = ThisWorkbook
Set ws = ThisWorkbook("Store")
Set rng = ws("UsedRange")




    
    'Variable values to determine if couple or not for decision completion


    Clmt = ComboBox1.Value & " " & Surname.Value
    Partner = ComboBox2.Value & " " & PSurname.Value
    Couple = Clmt & " and " & Partner




With wdDoc
    
    Call Module1.UpdateBookmark(wdDoc, "Title", ComboBox1.Value)
    Call Module1.UpdateBookmark(wdDoc, "ForeName", Forename.Value)
    Call Module1.UpdateBookmark(wdDoc, "SurName", Surname.Value)
    Call Module1.UpdateBookmark(wdDoc, "Nino", Nino.Value)
    Call Module1.UpdateBookmark(wdDoc, "AddressL1", AddressL1.Value)
    Call Module1.UpdateBookmark(wdDoc, "AddressL2", AddressL2.Value)
    Call Module1.UpdateBookmark(wdDoc, "AddressL3", AddressL3.Value)
    Call Module1.UpdateBookmark(wdDoc, "PCode", Pcode.Value)
    Call Module1.UpdateBookmark(wdDoc, "OPAmount", OPAmount.Value)
    Call Module1.UpdateBookmark(wdDoc, "iDate", Format(iDate.Value, "dd mmmm yyyy"))
    Call Module1.UpdateBookmark(wdDoc, "opStartDate", opStartDate.Value)
    Call Module1.UpdateBookmark(wdDoc, "opEndDate", opEndDate.Value)
    Call Module1.UpdateBookmark(wdDoc, "Reason", Reason.Value)
    Call Module1.UpdateBookmark(wdDoc, "RevisedAmt", Revise****t.Value)
    Call Module1.UpdateBookmark(wdDoc, "Breakdown", Sheets("Store").UsedRange)
        'Update the Word document's cross-references.
    wdDoc.Fields.Update
       
          
End With
    answer = MsgBox("Do you wish to Print the UC371 before saving?", vbYesNo + vbQuestion, "Print")


    If answer = vbYes Then
    wdDoc.PrintOut
    Else
    'do nothing
     End If
         
     wdDoc.SaveAs strFilePath & "UC371.doc"
     wdDoc.Close True
     
  


'Release system memory that was reserved for the two Object variables.
    Set wdDoc = Nothing
    Set wdApp = Nothing
'Close Word Doc
    Call CloseWordDocuments
'Open created Folder


    'Call Module1.print_preview
      
    


    Shell "explorer.exe " & strFilePath, vbNormalFocus


 


End Sub
```

I hope you can help me, here is the code i have tried but nothing happening. Would really appreciate it as would let me finish this project.


----------



## Macropod (Feb 7, 2018)

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

If your range is to be inserted as a table, you'll need to use either copy/paste or, if you start of with a basic table in-situ in the Word document, populate it,one Excel cell at a time, and add rows on the fly. If your range is not to be inserted as a table, you'll need to either copy/paste as a table, then convert the table to text, or write it out one Excel cell at a time.


----------



## Desmondo (Feb 7, 2018)

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



Macropod said:


> If your range is to be inserted as a table, you'll need to use either copy/paste or, if you start of with a basic table in-situ in the Word document, populate it,one Excel cell at a time, and add rows on the fly. If your range is not to be inserted as a table, you'll need to either copy/paste as a table, then convert the table to text, or write it out one Excel cell at a time.



Thanks for the speedy reply the data is in a table and I want to copy the full thing over. I managed to copy it paste it to a blank word doc. But need to insert it at a specific bookmark. Thanks for the pointers I will give it a go


----------



## Macropod (Feb 7, 2018)

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

Try something like:

```
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
```
which you'd call with code like:
Call PasteBookMark(wdDoc, "BkMk")
_after _copying the source range to the clipboard.


----------



## Desmondo (Feb 8, 2018)

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

Been trying this all day but still no joy, i sort of understand what you are doing. But getting compile error, wrong number of arguments or invalid property assignment. Not sure why


```
''''' Paste excel tablesSub 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
```

And the rest of the code


```
b uc371()

'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.
    Dim answer As Variant
    Dim strFolderName As String
    Dim strSurname As String
    Dim strNino As String
    Dim strFilePath As String
    Dim Partner As String
    Dim Clmt As String
    Dim Couple As String
    Dim aPPPath As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim tbl As Excel.Range
                   
    
        


'Get Surname from control
    strSurname = Me.Surname
    ' Get nino  from from control
    strNino = Me.Nino


    strFolderName = strSurname & " " & strNino
    
    strFilePath = Application.ThisWorkbook.Path & "\" & "Archive" & "\" & strFolderName & "\"
    CreateObject("WScript.Shell").PopUp "Creating your UC371!", 1
           


    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 = Application.ThisWorkbook.Path & "\" & "Templates" & "\" & "UC371.dotx"


'End If


    
    'Variable values to determine if couple or not for decision completion


    Clmt = ComboBox1.Value & " " & Surname.Value
    Partner = ComboBox2.Value & " " & PSurname.Value
    Couple = Clmt & " and " & Partner
    
    Set wb = ThisWorkbook
    Set ws = Sheets("Store")
    Set tbl = ws.Range("A1:C1", Range("A" & Rows.Count).End(xlUp))
    tbl.Copy
        
                




With wdDoc
    
    Call Module1.UpdateBookmark(wdDoc, "Title", ComboBox1.Value)
    Call Module1.UpdateBookmark(wdDoc, "ForeName", Forename.Value)
    Call Module1.UpdateBookmark(wdDoc, "SurName", Surname.Value)
    Call Module1.UpdateBookmark(wdDoc, "Nino", Nino.Value)
    Call Module1.UpdateBookmark(wdDoc, "AddressL1", AddressL1.Value)
    Call Module1.UpdateBookmark(wdDoc, "AddressL2", AddressL2.Value)
    Call Module1.UpdateBookmark(wdDoc, "AddressL3", AddressL3.Value)
    Call Module1.UpdateBookmark(wdDoc, "PCode", Pcode.Value)
    Call Module1.UpdateBookmark(wdDoc, "OPAmount", OPAmount.Value)
    Call Module1.UpdateBookmark(wdDoc, "iDate", Format(iDate.Value, "dd mmmm yyyy"))
    Call Module1.UpdateBookmark(wdDoc, "opStartDate", opStartDate.Value)
    Call Module1.UpdateBookmark(wdDoc, "opEndDate", opEndDate.Value)
    Call Module1.UpdateBookmark(wdDoc, "Reason", Reason.Value)
    Call Module1.UpdateBookmark(wdDoc, "RevisedAmt", Revise****t.Value)
    Call Module1.UpdateBookmark(wdDoc, "EarningsTotal", APCalculator.EarningsTotal.Value)
    Call Module1.PasteBookMark(wdDoc, "BreakDown", tbl.PasteSpecial)
     
         
        
        'Update the Word document's cross-references.
    wdDoc.Fields.Update
       
          
End With


     wdDoc.SaveAs strFilePath & "UC371.doc"
     wdDoc.Close True
     
  


'Release system memory that was reserved for the two Object variables.
    Set wdDoc = Nothing
    Set wdApp = Nothing
'Close Word Doc
    Call CloseWordDocuments
```


----------



## Macropod (Feb 8, 2018)

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

It's hardly surprising it doesn't work - you're not passing the required parameters. As per post #25 , there are only 2, but you're trying to pass 3...


----------



## Desmondo (Feb 8, 2018)

Do you mean like this without the wdObject as that already referenced in the the with wdDoc statement?

 Call Module1.PasteBookMark("BreakDown", tbl.PasteSpecial) if so its a type mismatch?


----------



## Macropod (Feb 8, 2018)

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


----------



## Desmondo (Feb 8, 2018)

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

It goes through without error but doesn't produce the documents. Sort of just flashes and then disappears.

Hold on its something else unrelated to your code


----------



## Desmondo (Oct 19, 2017)

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 (Feb 8, 2018)

Macropod said:


> 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 (Feb 8, 2018)

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 (Feb 8, 2018)

Macropod said:


> 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 (Feb 8, 2018)

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.


----------

