Using Excel Extract Content from Word Doc

[ChrisOK]

Had some code (a sub) that performed this long ago; can't find-- searched every advanced search hit on this site and haven't found anything yet - hoping a guru can help! Used to be able to attach sample of what the source content looks like and how I'm needing it laid into the excel file to try to make this easier; appears this option is gone? - Cross posting so I can attach a helpful file attachment example and needing help rather soon so the more eyes the better:
Using Excel VBA Extract Content from Word Doc
I know there's a ton of brilliant friends of many years here on MrExcel that can probably figure something out without a visual - but the link is available if needed!

*Do not need the code to open any files
(User will already have open 1 Excel file (ExampleExtractor.xlsm) and 1 Word file (LogRuleSourceXX.doc) open before running the SUB.
They will open each Word file one at a time -- and there's a large folder full - so I'm needing a sub like this again to expedite ongoing extractions).

THE 4 BASIC RULES THAT THE SUB SHOULD PERFORM:
1==If excel finds one of these rule words in All Caps [IF,AND,OR,PERFORM,THRU] in the Word doc, then copy the content that sits to the immediate RIGHT of that rule word & paste into Excel column C. (copy until a space occurs I think will work)

2==Then, pick up (copy/paste) the content that sits on the immediate RIGHT of the equals sign into Excel column D
(sometimes that content is in quotes - sometimes it's not - so not using quotes in the definition is probably more accurate (to just say in the code to pick up everything to the right of the equals sign on that same line) - and I'll parse off any extra garbage that I don't need)

3==Need the code to look at the Filename of the Word doc and extract the 12th+13th char position, paste that into Col E of Excel

4==Last, the code should locate the "Output" content which always follows the rule word: [TO], copy/paste into Col F of Excel

That's it!

(I threw those RULE WORDS into Col A as an idea that the code could look to that column when performing it's LOOKUP/INDEX -
but it's probably much better to just hard code them into a line of code and manage them there -- if so, disregard column A)

Forever in debt to you if you can figure out a decent sub to expedite this painful process...
Thanks, Chris

 

[Macropod]

I copied all the code I had in that orig tester file, pasted it into 'ThisWorkbook' of a nice clean file, saved as another macro-enabled excel file and for some reason the code won't run at all!? (won't even get me to the point of selecting a folder)
I tried moving it to another file and the same result.. but when I return to the orig tester file, it runs fine.

The sub name is highlighted in yellow: Sub GetWordDocumentData()
and this of row 4 is highlighted in blue? wdApp As New Word.Application
Any idea why it keeps highlighting these and saying: "Compile Error User defined type not defined"?
I even tried saving, exiting Excel, reopening, trying again - not sure why I can't move code to a new file?

All that tells me is that you haven't set a reference to Word in the new workbook...

 

[ChrisOK]

All that tells me is that you haven't set a reference to Word in the new workbook...

Well, I'm glad I asked -- learn somethin' new every day --- had no idea that setting had to be set every time on every workbook. Assumed once set, it applied to Excel in general (similar to the wide variety of settings one can set within the FILE>OPTIONS area.

I'm accustomed to running a lot from a custom toolbar area and setting others up on the team with the same functionality so we all can be productive as possible to meet deadlines... That said, I wondered if this would work from there as well - so I've pasted the code into a Module within the PERSONAL.xlsm area and created a shortcut within a custom ribbon/menu to be able to use it on any file on the fly.

I made sure while the module was clicked, to go ensure that TOOLS > REFERENCES > Microsoft Word 16.0 Object Library was check-marked, saved, exited, opened a new unnamed Workbook, clicked the icon on my toolbar and BAM it worked! So-SO-KOOL!

Paul's a genius! Three cheers! Thanks for the trouble-shooting -- I'm sure it will help others as well!
Tomorrow, I'll go update the cross-posts to ensure everyone sees the awesome solution!
Thank you again, and again - let me know if there's anything I can do for you.. Happy New Year Paul!

 

[Macropod]

VBA References only apply to the workbooks on which they're set. If they were global, you could end up with a plethora of references being added unnecessarily and slowing things down.

Although adding the code to your personal workbook will work for you, it won't work for anyone else who might need to run the macro in your absence. Assuming there is a particular workbook the code needs to be applied to, adding it (and the shortcut) to the relevant workbook resolves that; otherwise, you could create a template that other workbooks could be created from and store that template in an appropriate folder that all likely users will have access to. See: https://support.office.com/en-us/ar...template-58C6625A-2C0B-4446-9689-AD8BAEC39E1E

 

[ChrisOK]

In this code, it is looking at the Filename, extracting a small chunk of that Filename and pasting it into a column --
Is there a line or 2 of code that can be added to make it use that same small Filename extraction and use it to name the tab (worksheet) that it's pasting it to?

I'm manually having to name each tab - and didn't realize what a pain/timewaster it would be -- hoping it's an easy thing?

 

[Macropod]

I'm not sure what you mean about "I'm manually having to name each tab" - the macro only populates a single worksheet with the data from all the documents it opens. If you wanted each document to be output to a different worksheet, you should have said so - and it could easily have been accommodated. Given the current scenario, which of the many possible documents' data would be used for the sheet name?

 

[ChrisOK]

Oh so sorry for the confusion - a lot has been going on - head spinning here too!
Let me explain:
YES, indeed I wanted all the NON-TABLE data extracted from the large batch of Word docs and pasted it all into a single Excel sheet (love it and need it that way to be able to evaluate all NON-TABLE data from multiple files).


-- but after the review, I was given the request to capture the TABLE data from all the same 30 files so it could be reviewed collectively as well.

So, now I've taken all TABLE data for each of the 30 files and pasted it into 30 sheets.
Each sheet (representing 1 Word file) holds anything from a few tables to many!

At that point, I was having to manually go name each tab/sheet with the same extraction you'd used previously.
(picked up the 12th and 13th character of the Word's file name). Example: "AA", "SD", YB"

Anticipating having to do that again in the future as more batches of files come in - so that's why I asked if there was a couple lines of code that could extract those same characters extracted in this post -- and paste them as a tab/sheet name rather than pasting it into a column like we did previously.

-------
Looking ahead:

Now that I've got a single Workbook with 30 sheets full of "TABLES content" this time, I've got to do some combining of table content. In other words, TABLE XXX from all 30 Word docs into one single big table for analysis... (will post separately as it is a diff topic respectively).

* But since you were able to miraculously extract 2 characters from the file name and paste it into an added column, I wondered if it could be pasted as a tab/sheet name. Hope that makes sense.. sorry again for the confusion - it's been a crazy month with long-long hours...

 

[Macropod]

From memory, the macro starts populating at row 2. So, assuming all your worksheets have E2 populated, the following should do the job:

Sub Demo()
Application.ScreenUpdating = False
Dim xlWkSht As Worksheet
For Each xlWkSht In ActiveWorkbook.Worksheets
  On Error Resume Next
  xlWkSht.Name = xlWkSht.Range("E2").Text
Next
Application.ScreenUpdating = True
End Sub

 

[ChrisOK]

LOVE IT! Perfect solution! Thx so much Paul!