VBA to loop and extract parts from multiple word files to one sheet Excel

[Ovisele]

Hi guys!

I know that there are at least 5-6 topics on how to extract certain parts of text from Word to Excel, but I am just kindly asking you to go over my request, as there are some specificities. I will try to be brief:

• I have over 5000 PDF and Word files that I need to extract data from. The word files are in majority so I have decided to extract the first page of each PDF (done that), convert that first page to .doc (done that) so now I have the same base.
• What I need to do:
  • Loop through each word file (1-page document)
  • Extract only parts of data (see point 4)
  • Insert those date in an excel sheet for further analysis
• The entire page looks like the one in the image – the black boxes are not relevant
• The information that I need to extract is highlighted in yellow
• The problem is that in the last section, the Secondary diagnostics can vary from 2-3 to over 20 positions, depending on the patient. I was considering to use wildcards but I am not sure that this is a good approach. In fact, this variability from patient to patient is the main problem, otherwise I could (possibly) have extracted the data with an automated tool.

Sample word file here: Sample Word.docx

Many thanks for taking the time to read this! I really appreciate it!

 

[kennypete]

This is close to mission impossible since you have quite unstructured Word docs. Presuming they are all like your example, what looks like could be reliably find-able isn't;
it's full of tabs, column breaks, section breaks, etc., so trying to reliably extract content from this mess is not likely to be fruitful. You may get some way, but I expect there will be some level of review required of the output.

For example, you may find "Pana la..." with MS Word Regex

(presuming it's always followed by a tab character!) whereas "Sectia..." is not actually followed by "Cardiology" at all - it is for humans, not for a machine, where it's followed by a return and "De la".

The diagnostics could be found (in this example) with something like Secondary diagnostics:?^n^13(*)^13(*)^13(*)^13(*)^13(*)^13(*)^13(*)^13(*)^13 but who knows whether a different doc has the column break, the tiny paragraph mark, etc.?

I suggest going back to the source files and seeing whether a more reliable/structured input could be sourced.

 

[Ovisele]

Thank you very much for the effort of responding. Indeed, there is a total mess in the word files Much appreciated!