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