Office 2010: Transfer Form Content Control Data into Excel Database - Macro Help

Hello,

I am trying to find a macro that will copy data entered into form fields in a word document and into an excel database. I have seen other macros which take all forms in a folder and populate a database, but since this database will be used over time I would prefer that I can have word forms populated into the database as they come in (instead of as one bulk action).

The main issue I am running across is that I am using command controls to create text fields, date fields and checkboxes. The other macros I have seen copy the form fields directly into excel, importing an actual checkbox "character" into my excel instead of just a value that I can actually use in excel (i.e. 1,0 or Y,N).

Therefore I would like help creating a macro that can:
-Identify a specific file (or active word sheet) and copy filled in word content controls into excel
-Paste the data into a new row in excel
-Make sure that the checkboxes are copied as values (ideally 0,1) instead of as actual textbox symbols

I have attached a copy of a sample form. I cannot create the form in excel because the people I am sending it to won't want to fill out a form in excel (they prefer word).

Thank you very much

Hello,

Sorry for the delay, I have been away from the computer for the past couple days.

I do not have an excel document because I have simply been trying a bunch of things, failing and then restarting with a blank excel.

The best macro I have been able to find so far is the following:

 
Sub GetFormData() 
     'Note: this code requires a reference to the Word object model
    Application.ScreenUpdating = False 
    Dim wdApp As New Word.Application 
    Dim wdDoc As Word.Document 
    Dim CCtrl As Word.ContentControl 
    Dim strFolder As String, strFile As String 
    Dim WkSht As Worksheet, i As Long, j As Long 
    strFolder = GetFolder 
    If strFolder = "" Then Exit Sub 
    Set WkSht = ActiveSheet 
    i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row 
    strFile = Dir(strFolder & "\*.docx", vbNormal) 
    While strFile <> "" 
        i = i + 1 
        Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False) 
        With wdDoc 
            j = 0 
            For Each CCtrl In .ContentControls 
                j = j + 1 
                WkSht.Cells(i, j) = CCtrl.Range.Text 
            Next 
        End With 
        wdDoc.Close SaveChanges:=False 
        strFile = Dir() 
    Wend 
    wdApp.Quit 
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing 
    Application.ScreenUpdating = True 
End Sub 
 
Function GetFolder() As String 
    Dim oFolder As Object 
    GetFolder = "" 
    Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0) 
    If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path 
    Set oFolder = Nothing 
End Function

The issue with the code above is it opens up and imports all the data for a folder (instead I would like to select specific files) and when you make a checkbox content control (see the Word doc attached), it copies over the checkbox symbol instead of a 1/0 or True/False value.

If I could modify the code to fix those two issues it would be perfect.

Thank you