Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 43

Thread: Import text from word document into excel

  1. #1
    VBAX Regular
    Joined
    Jul 2012
    Posts
    9
    Location

    Exclamation Import text from word document into excel

    Hi... Dear all

    I am struggling with finding code to help me extract text from word documents into an excel sheet...


    Basically: I have 300 word documents that contain the same data for different individuals.. I need to come up with a code that would access the word documents one at a time, search the word document for certain words (e.g. "NAME" or "DOB") then extract what lies next to that name back into a field in excel.

    I really am lost.. and was hoping to get some quick pointers!!

    any help much appreciated..
    what goes around comes around

    thanks

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Do you want to run this from Word or Excel (I'd suggest Excel)?

    In the Word documents, are the required data in specified locations (eg formfields, particular cells in a table, etc), could there be other text, etc in the same paragraphs?
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Regular
    Joined
    Jul 2012
    Posts
    9
    Location

    Red face Thanks Macropod

    Thanks macropod for replying back..

    Basically:
    I have around 300 radiology reports on 300 different patients. The data in each report is pretty standard in all, but I inherited all these reports in word documents which were not formated into fields or delimited or had coma identifiers extra..
    Just as an example:

    Name: XXXXX
    DOB: XXXXXX
    Address: XXXXXX

    LVEDD=XXXX
    LVESS=KKKK

    TECHNIQUE:
    1. YYYYYY
    2. HHHHHHh

    So what I basically want to do, is try and parse/extract the data for the different patients and insert it in a database for later processing.

    It will take me ages to input manually and I was hoping to come up with some way to automate the whole process...

    Many thanks for all the help...
    PS: am still a medical student, and am a little bit challeneged when it comes to automating processes... but all the help I get from u guyz much appreciated!!!

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Hi mayseed,

    It would be helpful if you could post a sample document (no sensitive data) so that I could see the complete document structure.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    VBAX Regular
    Joined
    Jul 2012
    Posts
    9
    Location

    Red face

    Thanks for all your help!! Just knowing ur keen to help made my day.. I attached a sample report.. basically all reports are structured similarly. What I want to extract is the technique, different numbers.. the measurements are always preceded by the same word (i.e. ESD = xxx) and so on...

    thanks once again!
    Attached Files Attached Files

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    See attached workbook. It has a macro named 'UpdateData' that will populate the columns for which names exist. Simply point the macro's browser to the folder containing the files you want to process.

    As I'm not sure which bits of the remaining data you're after, I haven't finished the code yet. If you could clarify what's supposed to happen where multiple paragraphs are involved, which other fields you want and what you want to do about the bracketted 'normal' ranges, more progress can be made.
    Attached Files Attached Files
    Last edited by macropod; 07-08-2012 at 06:05 PM.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    VBAX Regular
    Joined
    Jul 2012
    Posts
    9
    Location

    Talking

    I am so grateful for u macropod.. i wish i can repay u for this.. this is brilliant.. so its working nicely... i am going to go through the code and see if i can adjust it..
    when it comes to the normal ranges i dont really need them.. all i need is the actual number..

    thats all..
    ill keep u posted with progress...

    U MADE MY DAY!! I want to cry!!
    thanks!

  8. #8
    VBAX Regular
    Joined
    Jul 2012
    Posts
    9
    Location

    Red face

    Hi again macropod!

    I tried dissecting the code and was trying to add some more fields but just couldnt figure out how to make it identify the different numbers in the document..

    i only need to extract the numbers (can ignore the normal range), some numbers are preceeded by "=" others are preceded by "-".

    Am not quite sure what to do

    thanks for ur help
    ur awesome

  9. #9
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    What to do depends on what you want to achieve. The code in the workbook I provided is already equipped to handle data preceded by ':' and '='. You just need to supply the prefixes. If you look where the code processes these, I'm sure you'll quickly figure out how to add the test for '-'.

    As you haven't answered the questions I asked in my last post, I can't really provide more specific advice.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  10. #10
    VBAX Regular
    Joined
    Jul 2012
    Posts
    9
    Location
    Thank you macropod...

    I tried to use the code to find the EDV, ESV values these use "=" symbol.. i did that by adding fields to the table containing the exact strings as spelled in the word document.. but it still wouldn't work..

    As for the data in the multiple paragraphs:
    for example.. the technique field: then the statements (4 in the sample file) need to be inserted into 4 different fields in the table.

    Again when I add "technique" to the table and then run the code, I only end up with the statement that follows the word technique.. the 4 other statements which occur on different lines get missed.. Any ideas on how to fix that?

    THANK U MACROPOD... I am almost there with getting the code to work.. all thanks to u...

  11. #11
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Hi mayseed,

    Try the attached. Do note there is a limit to what can be achieved with what you're working with. If your documents differ significantly from what you've posted, much of the code I've written could be invalidated - and coding around the difficulties might not be practical/possible.

    One thing to note is that I've assumed the doctor's name & title at the end (which I assume you don't want) always span two paragraphs. They get deleted during processing (the changes aren't saved) so that they don't get included with the extracted data.

    Quote Originally Posted by mayseed
    As for the data in the multiple paragraphs:
    for example.. the technique field: then the statements (4 in the sample file) need to be inserted into 4 different fields in the table.
    I don't think it's practical to do that, as you'll never know how many columns might be involved. For now, the code just puts them all in the one cell.
    Attached Files Attached Files
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  12. #12
    VBAX Regular
    Joined
    Jul 2012
    Posts
    9
    Location

    Exclamation THANK YOU SO MUCH!!

    THIS WORKS PERFECTLY.. I WANT TO CRY..

    THANK YOU SO MUCH MACROPOD THIS IS AMAZING.

  13. #13
    VBAX Newbie
    Joined
    Jun 2013
    Posts
    4
    Location

    Hi, sorry to jump on the end of this thread, but it seems to be almost whatI am looking form. Is it possible to use this to extract information from thecell to the right of the found word (from a table in word)? Rather than thenext tab?

    Any help would be appreciated

  14. #14
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Probably not without a major re-write. Working with tables requires a significantly different approach to the data matching.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  15. #15
    VBAX Newbie
    Joined
    Jun 2013
    Posts
    4
    Location
    Thank you macropod, shame
    I have found a way of pulling a table from multiple worddocuments, I am still trying to find a way to pull multiple tables frommultiple word documents, but the format is almost useless.
    I was hoping that the method above of searching for aspecific word (column heading) in the documents and pulling out associated datawould work better than what I have at the moment.
    Is that possible?

  16. #16
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Yes, it's possible. The code in this thread has all you need for looping though a series of documents; other threads here (in the Word Forum, probably) are likely to have code for looping through and finding content in tables.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  17. #17
    Thanks !!

  18. #18
    VBAX Newbie
    Joined
    Nov 2013
    Posts
    1
    Location
    Dear macropod, the macro you have posted is exactly something I am going for. However, the 'fields' in my document are not as clearly labelled and I haven't had much luck altering the code. Would you be kind enough to help me get just the first field sorted and I will give the rest a go myself?

    attach.jpg

    Would it be possible for it to just extract the bits in the rectangles (which aren't fields so seems quite difficult to extract from them)
    Last edited by iliauk; 11-08-2013 at 06:12 AM.

  19. #19
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    iliauk: This is now the third forum you've posted at concerning this topic. And at none of them have you had the courtesy of following the cross-posting etiquette, per: http://www.excelguru.ca/content.php?184, despite being reminded of this at the other two forums. I have no intention of discussing this topic with you here.

    Topic cross-posted at:
    http://www.msofficeforums.com/word/1...data-word.html
    http://www.excelforum.com/word-progr...ml#post3468375
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  20. #20
    VBAX Newbie
    Joined
    Oct 2014
    Posts
    1
    Location
    Quote Originally Posted by macropod View Post
    See attached workbook. It has a macro named 'UpdateData' that will populate the columns for which names exist. Simply point the macro's browser to the folder containing the files you want to process.

    As I'm not sure which bits of the remaining data you're after, I haven't finished the code yet. If you could clarify what's supposed to happen where multiple paragraphs are involved, which other fields you want and what you want to do about the bracketted 'normal' ranges, more progress can be made.
    Thanks Macropod. I have a need to read word document and posting this to be able to download the attached code with the hope that I can get information I need to update my macro.

Posting Permissions

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