Consulting

Results 1 to 6 of 6

Thread: Word Macro/VBA help

  1. #1

    Smile Word Macro/VBA help

    Calling all VBA wizards - Please help if you can!!!

    I am using the below macro in word to pull data into a template from excel using mail merge. Essentially, I have a database that outlines data by ticker (which are identified by anywhere from 1 - 5 letter symbols (example Google = GOOGL and Sprint = S)). I want my macro, when run, to allow me to type in a certain ticker into an input box and the macro (along with the mail merge) will search the database for that ticker and then will populate the mail merge document with applicable data. Everything works really well for those tickers with 3 - 5 letters. The problem I am running into is when I type in tickers that have 1 or 2 letters. What happens is instead of searching the document for the exact match of the ticker that I input, it finds the first instance of that string of letters. For example, if the ticker was AM the macro would pull in data for AKAM which comes before AM alphabetically and is therefore the first instance of AM in the document. My question is: Is there a way to write a macro just like I have below (since it mostly works) that would only populate data relevant to the EXACT ticker I type in rather than just searching the document for the first instance of the letters I type in. My current macro is below:

    Sub Macro()
    Dim numRecord As Integer
    Dim Ticker As String
    Ticker = InputBox("Enter the Ticker:")
    Set dsMain = ActiveDocument.MailMerge.DataSource
    If dsMain.FindRecord(FindText:=Ticker, Field:="Ticker") = True Then
    numRecord = dsMain.ActiveRecord
    End If
    End Sub

    I have posted to this forum before with this same question and over 400 people viewed the thread but no one responded. If this isnt possible to do, it would be great to know that too. I would really like any insight even if it doesnt give me a perfect solution.

    Thanks so much for the help ahead of time!!!!!

  2. #2
    VBAX Contributor
    Joined
    Jun 2014
    Posts
    107
    Location
    So would " AM " versus "AM" work? Basically you are adding the space to separate from AKAM.

  3. #3
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    Some variation of this:

    Sub Macro()
    Dim numRecord As Integer
    Dim Ticker As String
    Dim dsMain
    Ticker = InputBox("Enter the Ticker:")
    'Set dsMain = ActiveDocument.MailMerge.DataSource
    Dim oRng As Range
      Set oRng = ActiveDocument.Range
      With oRng.Find
        .Text = "AM"
        .MatchCase = True
        .MatchWholeWord = True
        If .Execute Then
          oRng.Select
          MsgBox "Do whatever"
        End If
      End With
      'If dsMain.FindRecord(FindText:=Ticker, Field:="Ticker") = True Then
      '  numRecord = dsMain.ActiveRecord
      'End If
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    I already answerd this question in: http://www.vbaexpress.com/forum/show...n-a-word-macro
    Did you bother to read it?
    Quote Originally Posted by kpangman View Post
    I have posted to this forum before with this same question and over 400 people viewed the thread but no one responded. If this isnt possible to do, it would be great to know that too. I would really like any insight even if it doesnt give me a perfect solution.

    As per http://www.vbaexpress.com/forum/faq...._new_faq_item4, kindly don't start multiple threads on the same topic.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Quote Originally Posted by kpangman View Post
    Sadly that didnt work - the macro wouldnt even run
    [QUOTE=macropod;399855]I already answerd this question in:
    Did you bother to read it?


    I'm sorry, I did not see the original comment you made on the other thread. I assumed something was wrong with my first thread since I didnt see any responses. I did try yours just now and the macro wouldnt run.

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Thread closed. You may resume the discussion in your original thread.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

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