Consulting

Results 1 to 14 of 14

Thread: Solved: Automating user merge field insert via a toolbar.

  1. #1

    Solved: Automating user merge field insert via a toolbar.

    I already have a program that will take a word document with merge fields & replace about 150 merge fields with data from various sources, merge it & present it to the users. This all works fine & dandy. This works with over 31 different merge documents that we will give to the user.

    I would like to be able to have the user create their own document (or modify an existing one) and have a toolbar that lets them select from a toolbar drop down (or better yet a layered menu) and insert one of my 150 predefined merge fields.

    Any help pointing me in the right direction would be greatly appreciated. I have searched the web for hours and can't seem to find what I want. However, this seems to be one of the best sites I have run across.

    Thank you.

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Ben,
    I put together a small utility for inserting text stored in an excel file to which I've made some adaptions which may suit your purpose. The mergesource is set by the selection of the option button, and the available mergefield names for each source are stored in the excel file.
    MD
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    It occurred to me I could do without Excel for the limited amount of data, so here's a revised version.
    MD
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    Thanks, that very much like what I was looking for, and something to point me in the right direction. Done tons of coding from vb, but never in vba.

    Of course this morning I found out that the header string is limited to 256 characters so I spent most of the day changing everything to DocVariables. But this should work just fine with DocVars instead of merge fields with just a bit of tweaking.

    DocVars will work fine, because we are just creating 1 letter at a time, and we aren't producing junk mail, etc.

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Glad to be of help. The one little thing I couldn't get to work was leaving the document with the focus, after the field was inserted (making it simpler to move to the next location), but I may get this solved yet. If you happen to know how to do this, please let me know.
    MD
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    That I figured out how to do though it was the 3rd thing I tried. VBA is quite different from VB.

    Add this or similar line of code
    OptionButton1.SetFocus
    as the last line of
    UserForm_Initialize

  7. #7
    It didn't take too long to switch it to document variables. Thanks again MD.

    For deployment, I'll just copy this Word Document to a "template" file & then launch word with the copied file?
    Is that all I have to do, & instruct the user to save the file in the proper folder so I pick it up when I merge it?

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    That should be OK. I've been playing around with this a bit more (for my own amusement) and this pre-Beta version should pick up on the original data source and list other files of the same type (up to 5) in the same location.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    I should be getting a book soon, but I'm having problem with the Selection pointer and it doesn't quite make sense. If I use

    ActiveDocument.Fields.Add Range:=Selection.Range, _
    Type:=wdFieldDocVariable, _
    Text:=FetchCodeAz(ListBox1.Text)

    The code is right after where the cursor is when Word lost focus, but if two codes are inserted in a row, the order will be reversed as to what is intuitive. So how would I move the Word cursor forward after the insert?

    Alternatively I've tried this:

    Selection.Fields.Add Range:=Selection.Range, _
    Type:=wdFieldDocVariable, _
    Text:=FetchCodeAz(Lis

    With this code, the order is fine, but all the inserts are selected. So in this case I'd just like to set the selection to a length of zero.

    I feel like an idiot asking, but such a simple task should not take so long to research & beat my head.

    When you get out of pre-beta, I just might take that code & tweak it for DocVariables.

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Ben,

    I'm off on holiday for a couple of weeks.

    MD

    Anyone else out there... can you assist?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    Thanks for all of your help MD I really appreciate it.

    Have a great Holiday Season!!!!

  12. #12
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Would this work for you?
    [vba]
    ActiveDocument.Fields.Add Range:=Selection.Range, _
    Type:=wdFieldDocVariable, _
    Text:=FetchCodeAz(ListBox1.Text)

    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.EndKey Unit:=wdLine
    [/vba]

  13. #13
    Thanks Jacob,

    I had just figured it out & was about to post this. as this works. I guess I finally figured out some of these goofy objects.

    I take it two statements would move the cursor down 1 line and then move it to the end of that line. Which is not bad. What I did was count the 31 characters for { DOCVARIABLE /* MERGEFORMAT } and add the length of the variable string, then append a space and move past that.

    Thanks I think this part of the project is just about wrapped.

    I really like this site. There are some smart people who hang around.

    [vba]
    Private Sub AddIt()
    Dim Ct As Integer

    If ListBox1.Text = "" Then Exit Sub
    Selection.Fields.Add Range:=Selection.Range, _
    Type:=wdFieldDocVariable, _
    Text:=FetchCodeAz(ListBox1.Text)
    Ct = 31 + Len(FetchCodeAz(ListBox1.Text))
    Selection.MoveRight unit:=wdCharacter, Count:=Ct
    Selection.InsertAfter " "
    Selection.MoveRight unit:=wdCharacter, Count:=1

    AppendLabel ListBox1.List(ListBox1.ListIndex)
    End Sub
    [/vba]

  14. #14
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Glad you got it straightened out.

    Take Care

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •