+ Reply to Thread
Results 1 to 6 of 6

Replace selected text in Word document with value from Excel Spreadsheet

  1. #1
    Registered User
    Join Date
    01-13-2022
    Location
    Sanford, NC
    MS-Off Ver
    O365
    Posts
    4

    Question Replace selected text in Word document with value from Excel Spreadsheet

    Hello all,
    This is my first post.
    I am a procedure writer at a power plant. In short, I need to be able to manually select text (a Document Number) in my Word document, and have the Word macro search that text string for data (the corresponding Document Title) in an Excel spreadsheet found on our lan. The macro will then replace the selected text in the Word document with the data pulled from the spreadsheet.
    Rather than making a huge post, I have attached a Word document with specifics, plus a sample source Excel spreadsheet from which data will be pulled.
    Any help (even feedback about me doing this wrong) would be greatly appreciated! I want to learn.
    Attached Files Attached Files
    Last edited by rokbottoms; 01-13-2022 at 04:12 PM. Reason: Solved
    Tom Bottoms
    Duke Energy
    Harris Nuclear Plant

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,128

    Re: Replace selected text in Word document with value from Excel Spreadsheet

    What is your question? Does your code work? Also I notice you just put the code as text in the file, rather than providing a macro-enabled file. Do you have a macro-enabled file? What action do you want to take to invoke the macro?

    I'm guessing it does not work. You can't read a .xlsx as a text file. I would open it as an Excel file in Excel and go directly to the matching entry with Find, and then simply replacing Selection (no need for Find & Replace since you already have the target text selected), rather than looping every entry and executing Replace on it. All of this can done within a Word macro.

    If you think that's what you want to do I can look at what it would take to put together that code.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    01-13-2022
    Location
    Sanford, NC
    MS-Off Ver
    O365
    Posts
    4

    Re: Replace selected text in Word document with value from Excel Spreadsheet

    Jeff,
    Thank you so much. Answers to your questions follow:
    What is your question? How do I use the text I have manually selected in Word (Document Number, e.g. OP-107) to conduct a search in Excel, select value to right of the search result (Document Title, e.g. CHEMICAL AND VOLUME CONTROL SYSTEM), and then copy/return/paste that value back to the Word document, replacing selected text (Document Number) with value (Document Title)?
    Does your code work? No, because it based on conducting a search in a .txt file, as you pointed out.
    Also I notice you just put the code as text in the file, rather than providing a macro-enabled file. Do you have a macro-enabled file? I replaced the docx with a docm. Is that what you meant? This macro is meant to go into my Normal.dotm template.
    What action do you want to take to invoke the macro? After manually selecting the text, I want to push a button on my QAT in my Word document, thus executing the search/replace.

    If you think that's what you want to do I can look at what it would take to put together that code. Yes! I think you have succinctly stated what I want.
    Last edited by rokbottoms; 01-13-2022 at 03:11 PM.

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,128

    Re: Replace selected text in Word document with value from Excel Spreadsheet

    Got it. Try this. I've tested it using your files, but locally. I'm hoping there will not be a problem with the remote server reference. I have no way to test that.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-13-2022
    Location
    Sanford, NC
    MS-Off Ver
    O365
    Posts
    4

    Re: Replace selected text in Word document with value from Excel Spreadsheet

    Thank you so much for you help thus far Jeff!
    When I execute, I get Run-time error '9':
    Subscript out of Range

    When I debug, it stops at line:
    Set Found = WB.worksheets("Procedure List 2").Range("A:A").Find(what:=Selection.Text, lookat:=xlWhole)

    Do you know where I might go from here?
    Last edited by rokbottoms; 01-13-2022 at 03:11 PM.

  6. #6
    Registered User
    Join Date
    01-13-2022
    Location
    Sanford, NC
    MS-Off Ver
    O365
    Posts
    4

    Re: Replace selected text in Word document with value from Excel Spreadsheet

    Jeff,
    After playing around with it a little, I got it to work. I removed the look:=xlWhole from the Set Found statement. Works fine after that. Thank you so much!!

    Tom

    Final code:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. vba code to find and replace text in word document from excel
    By sankpati in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 10-23-2019, 07:26 AM
  2. [SOLVED] Display user selected Text from a Combobox in the footer of Word Document
    By HangMan in forum Word Formatting & General
    Replies: 8
    Last Post: 07-02-2015, 05:58 AM
  3. [SOLVED] Find and Replace in Word document through Excel VBA
    By javi-xls in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-22-2014, 11:14 PM
  4. With excel to print a selected page from a word document
    By eb4927 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-15-2014, 05:54 PM
  5. Replies: 0
    Last Post: 09-04-2014, 03:32 AM
  6. Use Excel VBA for editing and find/replace in a Word document
    By drdavidge in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-05-2013, 06:10 AM
  7. Replace Text in Word Document with Image?
    By nickypatterson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-29-2011, 04:28 PM

Tags for this Thread

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