+ Reply to Thread
Results 1 to 12 of 12

Use Excel VBA for editing and find/replace in a Word document

  1. #1
    Registered User
    Join Date
    07-07-2006
    MS-Off Ver
    Excel 2007
    Posts
    60

    Use Excel VBA for editing and find/replace in a Word document

    Hey guys - I just discovered there are ways to use Excel macros to open and edit Word documents, but I can't seem to find much info or examples on how to do a few things. So far I have only been able to input text from my Excel sheet into a specific pre-defined location (via Word "bookmarks"). Anyone have any good websites or info that have some info on the following things?

    Excel Sheet for examples:
    A B
    Field Name - Data
    1 Seller John
    2 Buyer Jane
    3 Price $100
    4 Amount 50
    5 Terms Terms terms terms blah blah

    1) If i set up a Word document that has some text in there like [SELLER], can I have excel go through the Word doc and do a find/replace for [SELLER] and replace it with John above?

    2) Say I wanted to insert text somewhere in the Word doc at a bookmarked location, but I wanted to add formatting. For example, I want to underline or bold certain words that I insert. (Like in "terms" above). How can I do that?

    3) If I have a checkbox in Word.. how do I toggle checking that on/off from Excel?

    thanks.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Use Excel VBA for editing and find/replace in a Word document

    Hello drdavidge,

    Not surprising you haven't found much. Excel is a spreadsheet program and Word is text editor. There isn't a whole lot of crossover between them. One of the most crossover functions is a mail merge. Excel is suited for creating data tables and Word for formatting and arranging the text on the page.

    Word has its own Find and Replace function that can be accessed using VBA. All formatting of the document has to be done through Word 's VBA routines.

    Your first 2 questions are more Word VBA questions than Excel. The third question is an Excel question, but really doesn't seem germane to the previous two. In cases like this, you really should post the Excel workbook and the Word document.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Use Excel VBA for editing and find/replace in a Word document

    The simplest way of combining Excel Data in a word-document is using Document variables.{DOCVARIABLE} is a field in Word.
    You can assign an Excel value to the docvariable and then update in Word.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-07-2006
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Use Excel VBA for editing and find/replace in a Word document

    For #2, here's an example:

    Excel's cell A1 = "This is what Cell A1 contains"

    I have a bookmark in Word called "InsertLocation"

    Excel VBA Code:

    Please Login or Register  to view this content.
    Word ends up with having this at the "InsertLocation" bookmark location:

    "This is what Cell A1 contains" (Lacking the intra-cell bold / underline)

    How can I have the intra-cell formatting copy over? I tried replacing Range("A1").Value with Range("A1").Text and still had the same result.

  5. #5
    Registered User
    Join Date
    07-07-2006
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Use Excel VBA for editing and find/replace in a Word document

    Any ideas?

  6. #6
    Registered User
    Join Date
    07-07-2006
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Use Excel VBA for editing and find/replace in a Word document

    Anyone know how to do this?

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Use Excel VBA for editing and find/replace in a Word document

    As Leith suggested, use mailmerge in Word. That's what it was designed to do.
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Use Excel VBA for editing and find/replace in a Word document

    You didn't read my suggestion ?

  9. #9
    Registered User
    Join Date
    07-07-2006
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Use Excel VBA for editing and find/replace in a Word document

    Quote Originally Posted by snb View Post
    You didn't read my suggestion ?
    I tried this but the formatting did not carry over.. am I missing something?

  10. #10
    Registered User
    Join Date
    07-07-2006
    MS-Off Ver
    Excel 2007
    Posts
    60

    Re: Use Excel VBA for editing and find/replace in a Word document

    Anyone have any ideas?

  11. #11
    Registered User
    Join Date
    03-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Use Excel VBA for editing and find/replace in a Word document

    I have data sheet on which are as follows:
    pabitra 2000
    Sarika 1000
    pabitra roy 500
    sarika paul 5000
    pintu das 3000
    sarika 2000

    so sir i want to search by input box sarika and copy all the data of where included sarika copy to other workseet

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Use Excel VBA for editing and find/replace in a Word document

    roypabitra,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1