Copying Highlighted Text from a number of word documents to excel sheet macro

Hello all,

I am only versed in the basics of excel and need a macro to organize some data. Any help anyone could provide would be greatly appreciated or let me know if this is possible. I have a number of word documents containing DNA sequencing data, each word document has two highlighted regions of interest, highlighted in two separate colors. I need to consolidate these sequences into an excel sheet, where each row corresponds to a different set of sequence data and two columns that contain the two region of interests. I need a macro that could open a series of word documents with the same name as they appear on the excel sheet and copy the two highlighted sequences and paste it into the appropriate columns of excel. I have attached a example picture of how sheet needs to be set up. I would prefer to avoid having to go into each word document if possible as there are hundreds. Please let me know if this is possible. Any help you could provide would be greatly appreciated.
capture example.JPG
Thanks

Thanks for your help! I will give this a try ASAP and let you know how it worked out. Thanks again!

Hi daffodil11,

I tried out the macro you posted, unfortunately I get an error when I try to run it. Debugging mode showed the problem was with the line:

'~~> Establish an Word application object
Set oWordApp = GetObject(, "Word.Application")

Was there a change I needed to make with this line? Thanks again for your help

Hi Daffodil,

Sorry for the late reply and thanks for the continued help. I ensured that Microsoft word object library was enabled and that I changed the file path (Mine say 15.0 rather than 14.0 not sure if that matters). However, I still am getting the "Run-time error '429' ActiveX component can't create object" and debug still highlights the same line. Attached is a screen grab of references enabled, please let me know if you see anything problematic. capture example 3.JPGcapture example 2.JPG

Thanks

Thanks!!! Those two lines of code seemed to have fixed it and now it is working. However, the second set of sequences (those highlighted in grey) become offset and don't copy to the appropriate row. As show in the screen grab. If you know of way to fix this, that would be much appreciated, but if not, that's alright. It would not be too much trouble to cut and shift the entire column up. Thanks again for all you help you have saved me a lot of time. Thank you!! capture example 4.JPG

Hi daffodil11,

Thanks again for all your help. I was wondering if you could provide a solution to one more issue. In the excel sheet, there are some rows with clones that have not yet been sequenced and therefore have no word document equivalent with it's sequence data in the folder in which the macro is pulling the information from. This seems to cause an issue with the macro and it runs into an error when it cannot locate these files. Do you know if there is a line of code similar to the "On error resume next" code used earlier that will allow the macro to fill in the sequence data for those rows that have word docs in the folder but skip or ignore those that do not? Thanks again, and if this proves too complicated I can always just run the macro after every clone's sequence has been analyzed.

Thanks!

The macro appears to be successfully pulling data from the word documents now. however, it does not seem to be copying them to the correspond row. Instead just going in order whether or not the sequence data exists for that clone. For example, sequence data is complete for clone 1 and 3, but the macro fills the data for clone 1 and 2 which has no corresponding word document. Please let me know if you have any ideas of how to get it to fill in the corresponding row rather than just filling in the rows in order. Thanks again for any help you can provide, I really appreciate it!