#1
|
|||
|
|||
Help needed in excel VBA to transfer bookmarks
Hello everyone,
So I am new here and I have been struggling with a VB project. Basically I have created a simple user form in excel VB which asks the user to fill out the form and the information is transferred to a word template. So I would like to transfer IF text filled in by the user in the word template to another word template which then automatically creates a table with a column for number and a column for the transferred text. I have been looking at creating bookmarks and copying and pasting but have been struggling for weeks now. Please help me.. Thanking in advance Pat |
#2
|
||||
|
||||
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 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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Hi Gmayor
Thank you for your help. However, I wanted to transfer data from a WORD doc template to another WORD doc template. The code below you have provided transfers data from textbox in a userform to bookmark in Word doc. So Temp1 = worddoc 1 Temp2 = worddoc 2 User clicks button which opens Temp1, fills in information in Temp1 then clicks on another button on useform to open Temp2. IF certain bookmark in Temp1 is filled in then only it transfers to Temp2. Hope that makes sense I appreciate the help. Thanks |
#4
|
||||
|
||||
You want to transfer to a Word document from a Word template, but you asked in an Excel forum and you made reference to an Excel VBA userform, so it that was the solution I provided: A means of writing data from a userform to a Word bookmark. Now you seem to have moved the goal posts and frankly I now have no idea what it is you are trying to do.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#5
|
|||
|
|||
Sorry for the confusion.
My userform is based in Excel VBA. I would like to transfer data from one word template to another word template using a userform that is based in excel. |
#6
|
||||
|
||||
OK. I think . The code I originally posted demonstrates how to copy data from an Excel VBA userform to bookmark(s) in a Word document. If that is not what you want to do, what is it that you want to copy to either document and/or from one document to another and where does the userform come into it? A pair of buttons doesn't convey much.
The original code can easily be adapted to open a second document, and put data in that. However, you have referred to checking if a bookmark is filled in the first document to determine whether to write a value to a bookmark in the second document. That would be easy enough, but it is essential to learn where the new data for that bookmark comes from, and what causes the value in the bookmark to change. If it is being changed by the Userform then there is no need to evaluate the bookmark content as you can use the fact that you have written to it to determine that it has been changed. Clearly it won't be changed by the simple process of opening it. Why are there two buttons on the userform? Surely it would be possible to use only one to avoid even more confusion? The code could conditionally open a second document, or choose between two documents according to what information you have. A more complete description of the process is therefore required.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#7
|
|||
|
|||
So my code loads the userform which is used to generate 3 different types of checklists. On the userform there are 3 buttons for each checklist which opens up a word template.
The user generates a new checklist (checklist 1) which has bookmark 1. The user now needs to generate another checklist (checklist 2) which should contain text filled in from bookmark1 in checklist 1 copied into bookmark2 in checklist2. **So if text is present in bookmark1 in checklist1 should be transferred/copied to bookmark2 in checklist 2 upon clicking checklist2 button on the userform. I don't know if using bookmarks is the way forward, there might be another solution to this problem of copying/transferring bookmarks across documents. Thanking you in advance. I'm a bit slow |
#8
|
||||
|
||||
The more you try to explain it, the more confusing it gets.
Can you let us see the workbook with the userform (attach to the forum message via 'Go Advanced')? Remove any sensitive data first as we only need to see the userform and what it does.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
Tags |
bookmarks, transfer, vba excel |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Trying to populate Word bookmarks with excel UserForm | smd1112 | Excel Programming | 7 | 09-03-2014 09:42 PM |
Excel to word transfer | Raviraj | Word | 5 | 04-29-2014 02:47 AM |
Data transfer from Word tables to Excel | shoro | Word | 4 | 10-01-2013 05:08 AM |
Transfer Outlook info to Excel | methedr | Outlook | 1 | 05-24-2010 05:48 AM |
How to transfer Word 2003 Tables to Excel? | steeleye | Word Tables | 1 | 07-10-2009 04:18 PM |