![]() |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
|
Ok so I'm working on doing some data mining from an electronic medical record, but using Microsoft Word (or any other program that you guys may know of) to help me find keywords (from text that I paste into the document) without having to search for them manually.
How this would work ideally would be something like this: 1. have a chart with multiple specific keywords in Column A. 2. Paste text from electronic medical record into Microsoft Word (or any other program) 3. the macro searches for the keywords I assign to it 4. If keywords are found, it in the text field, it would paste the word immediately before or after the keyword. For example, I may have multiple keywords in this document, but one keyword that Word would look for would be a SHIM score. Hence, word would search the document for "SHIM" from within the text that I paste into the macro. I then paste a large amount of text into Word and it searches for SHIM, BUT the macro pastes the values found immediately before or after the keyword in the document. Let's say in the document it says "SHIM 25" so in the chart, under SHIM, it will display the number 25 for me automatically in the second column. Something like this would be immensely helpful in data mining. It would allow my team and me to enter data far quicker and produce lots of peer reviewed research in a much quicker fashion. This research would go on to help men all over the world with prostate cancer. Any assistance to help with this would be GREATLY appreciated. If it's not possible, I am also happy to accept this outcome but figured I would reach out to some experts in this forum first for help. Thanks so much in advance! |
|
#2
|
|||
|
|||
|
also if it's not possible to find the word after the search term, finding the search term alone would be helpful too.
|
|
#3
|
||||
|
||||
|
As someone who has, so far successfully, been treated for prostate cancer over the past few years, this is one cause I am happy to assist with, however without access to examples of the source documents and a list of what you want to search for and expect to find in relation to those texts, it is difficult to provide that assistance. However the following will find words or phrases from a list followed by a space and a number, which might help get you started.
I don't have the Mac version of Word, but it should still work. Code:
Sub Macro1()
Dim vFindText As Variant
Dim oRng As Range
Dim oColl As Collection
Dim oSource As Document, oTarget As Document
Dim i As Long, j As Integer
Set oSource = ActiveDocument
Set oTarget = Documents.Add
Set oColl = New Collection
vFindText = Array("SHIM", "WORD2", "WORD3") 'list the words or phrases to find
For i = 0 To UBound(vFindText)
Set oRng = oSource.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
Do While .Execute(FindText:=vFindText(i) & "[ .0-9]{1,}", _
MatchWildcards:=True)
oTarget.Range.InsertAfter oRng.Text & vbCr
oRng.Collapse wdCollapseEnd
Loop
End With
Next i
lbl_Exit:
Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
|
#4
|
|||
|
|||
|
THANK YOU so much, and I certainly hope your treatment(s) have gone as planned. I work alongside some of the top key opinion leaders in the USA so feel free to send me a PM with any questions about your care (should you have any).
OK so I'm really a total novice when it comes to this stuff. Basically, I inserted your code into the VBA editor in Microsoft Word, then tried to run it based on some text I pasted into a document. What happens when I run the macro is that it opens a new, untitled blank document, but does nothing else. I did attach a document that shows an example of what I'm trying to achieve, which would probably make this easier to understand. If you can provide any further assistance, I would appreciate it tremendously! |
|
#5
|
|||
|
|||
|
Graham is on the other side of the world and it will be awhile before he is back around. Based on your example scenario, I am assuming that the terms e.g., PSA will appear once in the text to process.
The following might work were you define the number of following words to include in your term definition table. See attached: Code:
Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oTbl As Table
Dim arrTerm() As String
Dim lngIndex As Long
Dim oRng As Range
Set oTbl = ActiveDocument.Tables(1)
ReDim arrTerm(oTbl.Rows.Count - 2, 1)
For lngIndex = 2 To oTbl.Rows.Count
arrTerm(lngIndex - 2, 0) = fcnGetCellText(oTbl.Cell(lngIndex, 1))
arrTerm(lngIndex - 2, 1) = fcnGetCellText(oTbl.Cell(lngIndex, 2))
Next lngIndex
For lngIndex = 0 To UBound(arrTerm, 1)
Set oRng = ActiveDocument.Range
oRng.Start = oTbl.Range.End
With oRng.Find
.Text = arrTerm(lngIndex, 0)
If .Execute Then
Select Case True
Case IsNumeric(arrTerm(lngIndex, 1))
oRng.Collapse wdCollapseEnd
oRng.MoveEnd wdWord, Val(arrTerm(lngIndex, 1)) + 1
oTbl.Cell(lngIndex + 2, 3).Range.Text = Trim(oRng.Text)
Case arrTerm(lngIndex, 1) = "T/F"
oTbl.Cell(lngIndex + 2, 3).Range.Text = "True"
End Select
Else
Select Case True
Case IsNumeric(arrTerm(lngIndex, 1))
oTbl.Cell(lngIndex + 2, 3).Range.Text = vbNullString
Case arrTerm(lngIndex, 1) = "T/F"
oTbl.Cell(lngIndex + 2, 3).Range.Text = "False"
End Select
End If
End With
Next lngIndex
lbl_Exit:
Exit Sub
End Sub
Function fcnGetCellText(oCell As Cell) As String
fcnGetCellText = Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)
lbl_Exit:
Exit Function
End Function
|
|
#6
|
||||
|
||||
|
I recommend you find a standard way to set up the source info before running the macro. Based on what you have said and the sample doc you posted, I would setup the document to start with a two column table, the first column would be filled with the key search terms and the second column would be empty waiting for the macro to run. Then following the table you could paste in the content from your EMR.
The macro that suits this layout and workflow might then look like this. Code:
Sub Extractor()
Dim oRng As Range, oTable As Table, oCell As Cell, oNextCell As Cell
Dim sFind As String, oRngCell As Range
Set oTable = ActiveDocument.Tables(1)
Set oRng = ActiveDocument.Range(oTable.Range.End, ActiveDocument.Range.End)
For Each oCell In oTable.Columns(1).Cells
sFind = Split(oCell.Range.Text, vbCr)(0)
Set oRngCell = oCell.Next.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.MatchWildcards = False
Do While .Execute(FindText:=sFind)
oRng.MoveStart Unit:=wdWord, Count:=-2
oRng.MoveEnd Unit:=wdWord, Count:=4
oRngCell.InsertBefore oRng & vbCr
oRng.Collapse direction:=wdCollapseEnd
Loop
Set oRng = ActiveDocument.Range(oTable.Range.End, ActiveDocument.Range.End)
End With
Next oCell
End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
|
#7
|
||||
|
||||
|
It seems that fellow contributors have been busy while I slept. The original macro didn't work because the search criteria are not as you said. The problem with manuscripts of free text is that they don't always conform to standards so they might not always give the expected results, but based upon your sample I would suggest the following, again bearing in mind that I have not tested it on the Mac version of Word.
It works on the premise that you paste the text into a document and run the macro. The macro adds a table at the top of the page and if the terms are found, it adds them to the first column and highlights them in the text. It then attempts to find the required values associated with them and puts them in the adjacent columns. As for my own treatment I was diagnosed and treated in an award winning oncology centre (one of the best in Europe) five years ago. All currently appears well though it took a long time to shake off the after effects of the hormone treatment that followed. I finished treatment two years ago. I see my oncologist every six months and I am still alive and well ![]() Code:
Sub Macro1()
Dim vFindText As Variant
Dim oRng As Range
Dim oTable As Table
Dim oCell As Range
Dim oDoc As Document
Dim i As Long, j As Integer
vFindText = Array("AUA", "SHIM", "PSA", "TURBT", "Frequency")
Set oDoc = ActiveDocument
oDoc.Range.InsertParagraphBefore
Set oRng = oDoc.Range
oRng.Collapse 1
Set oTable = oDoc.Tables.Add(oRng, 5, 2)
For i = 0 To 4
Set oRng = oDoc.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
Do While .Execute(FindText:=vFindText(i))
Select Case i
Case 0, 1, 2
oRng.MoveEndUntil "0123456789"
oRng.Collapse 0
oRng.MoveEndWhile "0123456789/."
Set oCell = oTable.Cell(i + 1, 1).Range
oCell.End = oCell.End - 1
oCell.Text = vFindText(i)
Set oCell = oTable.Cell(i + 1, 2).Range
oCell.End = oCell.End - 1
oCell.Text = oRng.Text
Case Else
Set oCell = oTable.Cell(i + 1, 1).Range
oCell.End = oCell.End - 1
oCell.Text = vFindText(i)
Set oCell = oTable.Cell(i + 1, 2).Range
oCell.End = oCell.End - 1
oCell.Text = True
End Select
Loop
End With
Next i
lbl_Exit:
Set oDoc = Nothing
Set oRng = Nothing
Set oTable = Nothing
Set oCell = Nothing
Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
|
#8
|
|||
|
|||
|
Graham,
Looks good. I would only change: oRng.MoveEndWhile "0123456789/" 'eliminated the period. |
|
#9
|
||||
|
||||
|
Greg
What if the number has a decimal point?
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
|
#10
|
|||
|
|||
|
Graham,
True. Didn't think of that as a decimal wasn't in the example text. So, yes leave your line as it and append to your existing: oCell.End = oCell.End - 1 oCell.Text = oRng.Text If Right(oCell.Text, 1) = "." Then oCell.Text = Left(oCell.Text, _ Len(oCell.Text) - 1) To eliminate the period (when it is a period). |
|
#11
|
|||
|
|||
|
Thank you everyone for your help! I will play with what you sent and get back with you all ASAP
|
|
#12
|
|||
|
|||
|
Hello everyone!
Thanks so much for your help! Can anyone tell me how I can input the latest code from gmayor into the document? I also got gmaxey's attachment and it seems like everything works GREAT! One question I had is how I would add additional rows and keywords to the table so I can change/modify the keywords so the word document searches for them automatically (for other studies, for instance that may have different keywords). If we had the ability to do this, it literally would be a GAME CHANGER. I could put out 2-3x the amount of research with a tool like this. I genuinely appreciate everyone's help in this matter! Even if it was a table with like 50 blank rows or something, but whenever I put a keyword into column A, it would do the search automatically in column B. Is something like this possible? Thank you thank you so much for your help!!!! |
|
#13
|
|||
|
|||
|
Take a look at the attached. It combines what I felt were the best ideas of both Graham and I where you start with a table to define the search terms.
|
|
#14
|
||||
|
||||
|
The problem with adding words - in the case of Greg's version to a table. In the case of mine to an array, is not the addition of terms to either, but the processing required to get the required text associated with those terms. Finding the terms is simple enough but it is the results associated with the terms that are at issue.
Greg has made some sensible choices to enable you to input terms and report TRUE, and also to select particular values a set distance from the found term e.g. XXX and I would have used Mr and not Mr X to find the age of the person. His version doesn't highlight the terms, but that's an easy addition. Unfortunately with manuscripts that do not conform to a standard pattern pasted into the document it can still be a bit hit and miss when trying to extract the results, and one sample is insufficient to produce more accurate results - and given the nature of what you are doing, I would have wanted the results to be accurate. Had I been doing this I would have started with a form with a fixed layout using content controls or fields to enter the required pieces of information, but that would mean changing the 'electronic medical record' that you paste from. What format is that medical record in? Would it be possible to read directly from that record as the chances are that it will be in a more structured format? If you want to supply some more information and some further samples including a full list of terms to find, you can send them to the contact link on my web site (add your forum username to the subject so it won't get swept away by my spam filters) and I will see what else needs to be done. I can share them privately with Greg (who is a friend of long standing) if you wish.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
|
#15
|
|||
|
|||
|
GMAXEY
Thank you SO MUCH for your help with this! GMAYOR I will certainly contact you with more details. The medical record is stored on a system called EPIC, which is an online electronic medical record system. I merely copy the data from EPIC and paste it into some sort of office program so that I can do searches on the text. I usually paste medical record information that can be anywhere from 6-12 pages long. Doing the search makes it far easier than reading each medical record one by one to find the data I need. Gmaxey, is there a way to perhaps include a text box that I can paste data in to or something so the macro knows what area to search for? I'm not sure how your macro searches for the data, like if it's limited to a certain space in the actual document? Thank you guys again so much. This is so very promising and it makes me VERY excited about how much time it could save/more data we can publish! |
|
| Tags |
| keyword, macro, search |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| How to find (highlight) two and more words in a list of 75k single words in Word 2010 | Usora | Word | 8 | 05-29-2018 03:34 AM |
Macro to search for specific words in a document
|
mike0215 | Word VBA | 2 | 11-28-2017 07:25 AM |
| VBA Word - Find & Apply Styles to Specific Words - Using Case Statement | jc491 | Word VBA | 17 | 12-26-2015 12:25 PM |
Need VBA For Macro On How To Remove Specific Words
|
netchie | Word VBA | 6 | 08-28-2012 03:37 PM |
Macro for highlighting specific number of words
|
icsjohn | Word VBA | 2 | 12-07-2011 06:44 PM |