Replace selected text in Word document with value from Excel Spreadsheet

[rokbottoms]

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.

[6StringJazzer]

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.

[rokbottoms]

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.

[6StringJazzer]

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.

[rokbottoms]

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?

[rokbottoms]

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.