How to extract value of MS Word form Content control box to excel

Question

Hello y'all, I have a folder with about 200 word documents that contain data I am trying to get into excel. The word template used has many content control plain text boxes with unique names. My users have filled out these word forms and now I need to get the data into a single excel sheet. I've tried power automate, VB script, etc. but I'm new to this and nothing is working.

as an example in the overview section I have three plain text content controls, (1)"Name", (2) "PSRB", (3)"overview".

I need to extract the value of those fields to an excel sheet.

Answer 1

Hello deepslp,

Welcome to Microsoft Q&A!

We streamline the process by using VBA to loop through all the Word documents in your folder and extract the content control data into a single Excel sheet. Here’s the steps on how you can do it:

Prepare Your Excel Workbook:

• Open a new Excel workbook and enable the Developer tab as mentioned earlier.

Open the VBA Editor:

• Press Alt + F11 to open the VBA editor.

Insert a New Module:

• Go to Insert > Module.

Add VBA Code:

• Copy and paste the following VBA code into the module:

Sub ExtractContentControlsFromFolder()
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim wdCC As Object
    Dim ws As Worksheet
    Dim folderPath As String
    Dim fileName As String
    Dim i As Integer
    Dim j As Integer

    ' Set the folder path
    folderPath = "C:\path\to\your\folder\"

    ' Create a new Word application instance
    Set wdApp = CreateObject("Word.Application")

    ' Set the Excel worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Initialize row counter
    i = 1

    ' Loop through all Word documents in the folder
    fileName = Dir(folderPath & "*.docx")
    Do While fileName <> ""
        ' Open the Word document
        Set wdDoc = wdApp.Documents.Open(folderPath & fileName)

        ' Loop through all content controls in the Word document
        For Each wdCC In wdDoc.ContentControls
            ws.Cells(i, 1).Value = fileName
            ws.Cells(i, 2).Value = wdCC.Title
            ws.Cells(i, 3).Value = wdCC.Range.Text
            i = i + 1
        Next wdCC

        ' Close the Word document without saving
        wdDoc.Close False

        ' Get the next file name
        fileName = Dir
    Loop

    ' Quit Word application
    wdApp.Quit

    ' Release objects
    Set wdCC = Nothing
    Set wdDoc = Nothing
    Set wdApp = Nothing
End Sub

Run the Macro:

• Press F5 to run the macro. This will loop through all Word documents in the specified folder, extract the content control values, and place them into the Excel sheet.

Make sure to replace "C:\path\to\your\folder\" with the actual path to your folder containing the Word documents. Let me know if the script work, I will be waiting for your response.

Regards,

Gao

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".