Help needed in excel VBA to transfer bookmarks

pat263 · #1 10-28-2014, 02:34 AM

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

gmayor · #2 11-08-2014, 11:19 PM

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

The following macros (in a normal Excel VBA module) will call the userform and process the results, putting the text from the userform text boxes into the Word bookmarks. If you run the form again the bookmarked values will change to reflect the new values. Put any code to interface with Excel in the main macro below and not in the userform Change the names of the userform, fields and buttons, document and bookmark names to reflect what you have:

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

pat263 · #3 11-10-2014, 05:50 AM

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

gmayor · #4 11-10-2014, 07:53 AM

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.

pat263 · #5 11-10-2014, 08:19 AM

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.

gmayor · #6 11-10-2014, 11:09 PM

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.

pat263 · #7 11-11-2014, 04:56 AM

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

gmayor · #8 11-11-2014, 07:56 AM

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.

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