VBA for putting in 450 word documents into excel

[KMik]

Hello,

I've searched the internet lands for a macro that could take over 400 job descriptions and move their different sections from WORD into excel. Essentially I want all of the job descriptions (which all have similar headers in Word), to have different columns per section and pull the data from word under those sections.

For example:

Title: RN
Cost Center: 4356
FLSA: Non-Exempt

Summary
Paragraph here of the summary of the job

Minimum Qualifications
1. Must possess a current license to practice professional nursing in the Commonwealth of Pennsylvania.
2. Certification by the American Association of Nurse Anesthetists (AANA).
3. Graduate of an accredited school of nurse anesthesia.

Preferred Qualifications
1. Previous experience in a Charge or supervisory position.

Essential Functions
1. Exemplifies St. Clair Hospital’s Customer Service Behavioral Expectations in all interactions. Treats customers with respect, compassion, and hospitality; anticipates, identifies, and responds to individual needs; makes a positive first impression by acknowledging customers; and demonstrates flexibility while maintaining priorities.
2. Under the supervision of the manager, prepares and maintains the CRNA work schedule assuring adequate staffing levels to cover all areas of the anesthesia service.

Title	Cost Center	FLSA	Summary	Minimum Qualifications	Preferred Qualifications	Essential Functions
RN	4356	Non-Exempt	Paragraph here of the summary of the job	Must possess a current license to practice professional nursing in the Commonwealth of Pennsylvania	Previous experience in a Charge or supervisory position.	Exemplifies St. Clair Hospital’s Customer Service Behavioral Expectations in all interactions. Treats customers with respect, compassion, and hospitality; anticipates, identifies, and responds to individual needs; makes a positive first impression by acknowledging customers; and demonstrates flexibility while maintaining priorities.
				Certification by the American Association of Nurse Anesthetists (AANA).		Under the supervision of the manager, prepares and maintains the CRNA work schedule assuring adequate staffing levels to cover all areas of the anesthesia service.
				Graduate of an accredited school of nurse anesthesia.

I have attached a copy of a job description, where most of the 400 job descriptions have the same format. Is this even possible, especially with the bullets for essential functions and minimum qualifications? We are converting to an online system and it'll be much easier to upload into the system from excel. Any help would be REALLY appreciated!

I also would like for the macro to see that there are 400 job descriptions within a folder and do it automatically without having to open up all 400 job descriptions.

Thank you so much in advance!

[snb]

This macro should be started from Excel.

Sub M_snb()
   With GetObject("G:\OF\CRNA charge new 110112.doc")
     sn = Filter(Split(Replace(Replace(Replace(.Content, vbTab, " "), vbCr & "Essential", vbCr & vbCr & "Essential"), vbCr & "Summary", "Summary"), vbCr & vbCr), " ")
     .Close 0
  End With
  
  For j = 0 To 6
    If j < 3 Then
       Cells(1, j + 1).Resize(2) = Application.Transpose(Split(sn(j), ":"))
    Else
       sp = Split(sn(j), vbCr)
       Cells(1, j + 1).Resize(UBound(sp) + 1) = Application.Transpose(sp)
    End If
 Next
End Sub

[KMik]

This is wonderful SNB! Thank you so much. Is there a way that it could open all of the word documents (one right after another) that are in one folder and do the same thing for the other 400ish word documents into the same excel word document?

Thank you again!

[mancubus]

yes. and i'm sure snb will provide the code.

but you should define in what way they will be written to the excel file.

each Word doc as separate worksheet?

merged in one worksheet? if so, how?

etc?

[SamT]

Thank you so much. Is there a way that it could open all of the word documents (one right after another) that are in one folder and do the same thing for the other 400ish word documents into the same excel word document?

Can you be more explicit? You know. Step by step what you need.

Just pretend that you're talking to a 6yo, we are programmers, after all.

[KMik]

I would want all of the word documents from the folder (which are all in the same template) to be put into ONE excel spreadsheet. For example it would look like this.

Title	Cost Center	FSLA	Summary	Minimum Qualifications	Preferred Qualifications	Essential Functions
Job 1	88383	Exempt	safjkdshfksda	min	1	1
				min	2	2
				min	3	3
						4
						5
job 2	43567	Non Exempt	dsfdsafdsafds	min	1	1
				min	2	2
						3
						4
Job 3	34532423	Exempt	sdfdsfdsa	min	1	1
						2

Does that help? Sorry for not being more explicit! Thank you again for helping. I only know a tiny bit about macros but not nearly enough to do all of this!

Thanks!

[SamT]

Should we assume that all the jobs on that worksheet came from the same Word Document?

How do we know what numbers to assign to Preferred Qualificaions and Essential Functions?

[KMik]

Sam that was just an example of what I wanted it to look like. In the word document, there will be numbered lists. Please see the document that I attached to my first post.

Thanks.

[snb]

Sub M_snb()
    y = 1
    c00 = "G:\OF\"
    sq = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & c00 & "*.doc"" /b/s").stdout.readall, vbCrLf), ":")
    
    For Each it In sq
        x = 0
        With GetObject(it)
            sn = Filter(Split(Replace(Replace(Replace(Replace(.Content, ":" & vbTab, " " & vbCr), vbTab, ""), vbCr & "Essential", vbCr & vbCr & "Essential"), vbCr & "Summary", "Summary"), vbCr & vbCr), " ")
            .Close 0
        End With
         
        For j = 0 To 6
            sp = Split(sn(j), vbCr)
            Cells(y, j + 1).Resize(UBound(sp) + 1) = Application.Transpose(sp)
            If UBound(sp) > x Then x = UBound(sp)
        Next
        y = y + x + 1
    Next
End Sub

NB. Just curious how mucht time/money this will save your company.