Change Check Box character to text

[StefM]

Hi

I have a word 2016 form with content controls, some of which are checkboxes.

Using a macro I got from this forum (from Macropod), I'm extracting the form data into Excel but the checkboxes are coming in as symbols.

I recorded a macro for Find / Replace but it changed every cell !

I want the ☒ (selected checkbox) to change to be Yes and the ☐ (unselected checkbox) to be No, with a macro and not find/replace each time.

Thanks
Stef

[SamT]

Show us the Code you got from Macropod. Simply Copy it in your VBA Editor, then in Our Post Editor, click the menu # icon, then press Ctrl+V

[StefM]

Sub GetFormData()

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 = "c:\users\myname\desktop\answers"
If strFolder = "" Then Exit Sub

Set WkSht = ActiveSheet
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(strFolder & "\RESOURCES QUESTIONNAIRE.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

[SamT]

Your code
With wdDoc
j = 0
For Each CCtrl In .ContentControls
j = j + 1
WkSht.Cells(i, j) = CCtrl.Range.Text
Next
End With


I don't speak VBA for Word, So you will have to research the ControlType Name

You need something like

With wdDoc
j = 0
For Each CCtrl In .ContentControls
j = j + 1
If ControlType(CCtrl) = "???" Then
If CCtrl = [True|0|Checked|Yes] Then '??? I dunno
WkSht.Cells(i, j) = "Yes"
Else: WkSht.Cells(i, j) = "No"
End If
Else: WkSht.Cells(i, j) = CCtrl.Range.Text
End If
Next
End With