Extract Data/controls from Word to Excel

Question

Currently I have a word document that is a questionnaire.

Format (Total of 30 questions):

Name:

1. Question
YES checkbox
NO checkbox
Comments

2. Question
YES checkbox
NO checkbox
Comments

The check boxes and comments are controls.

What I am trying to do is extract the checkbox and comment data into excel. For example,

1. Do you spend time under the sun?
YES checkbox is selected
Comments: I spend 2 days out of the week in the sun.

What I want it to display in excel would be.

Every time the macro is run, it should add the new information below an existing record (row).

I usually use Word and extract data from excel into word, but I have never tried this one.

This is what I came up with so far:

Sub Macro1()
    Application.ScreenUpdating = False
    Dim lRow As Long, i As Long, j As Long, iCtr As Long, cBox As CheckBox, StrFlNm As String
    With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
        SendKeys "%n *.doc ~"
        If .Show = True Then
            StrFlNm = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    Application.Volatile

    For Each cBox In ActiveSheet.CheckBoxes
        If cBox.Value = 1 Then
            iCtr = iCtr + 1
        End If
    Next cBox
    CheckedCount = iCtr
End Sub

It goes as far as selecting the file, but nothing happens. If i can identify how to apply it to one checkbox, I should be able to figure out how to do the rest. I will be updating the code as we go on, I have changed it numerous times.

Activated Microsoft Object and ran the new code. Once the file is selected, I am prompted with this message.

Sam Sam · Accepted Answer · 2019-06-19T20:35:23

The first half of your code does nothing more than give you a file name. At that moment, Excel knows nothing about the content of the document.
Here, you will need to open the document, and for that you need a reference to Word's object model. In Tools - References, select Microsoft Word nn Object Library.
Now, Excel can work with Word documents.

Do something like this:

Sub Macro1()
    Dim lRow As Long, i As Long, j As Long, iCtr As Long, cBox As ContentControl, StrFlNm As String

    With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
        SendKeys "%n *.doc ~"
        If .Show = True Then
            StrFlNm = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With

    Application.Volatile

    Dim Doc As Word.Document
    Set Doc = Word.Documents.Open(StrFlNm)
    Debug.Print Doc.Range.ContentControls.Count

    For Each cBox In Doc.Range.ContentControls
        If cBox.Checked Then
            iCtr = iCtr + 1
        End If
    Next cBox
    CheckedCount = iCtr
    Doc.Close
End Sub

Extract Data/controls from Word to Excel

1 Answers