Extracting checkbox data from a MS Word table

[bren_c]

Hi,

I've searched extensively for a solution to this but have yet to find one.

I'm trying to use a macro to extract a true or false value from a checkbox in a row within a word document table, and feed the true / false value into a field in an excel doc.

I'm fine with getting text from different rows within the table, but when I try to extract the checkbox in the same way as I extract text, I get a garbage character. I can't find any way to select the checkbox within the row and extract it's value.

Can anyone help?

thanks!

[macropod]

Without seeing your code, it's difficult to diagnose the problem. How are you trying to access the checkbox state? What kind of checkbox is it (formfield, ActiveX, Content Control)?

[bren_c]

Thanks for your reply Paul.

I'm using a Form Field checkbox.

At the moment I'm parsing through each table in a document, and if the (1,1) cell is equal to a given string, I process that table. I'm taking string values from other cells in the table and inserting them into cells in an excel worksheet.

However, I can't figure out a way to take the values of form field checkboxes within the the cells and convert them into a value which can be inserted into excel (i.e. true/false, 1/0). I can't single out a checkbox object within the cell to attempt to query it's value which I guess should be the first step.

Here's what I have working at the moment:

For iTable = 1 To wdDoc.tables.Count
    With .tables(iTable)
        ' copy cell contents from Word table cells to Excel cells
        If InStr(1, .cell(1, 1).Range.Text, "Test Case Identifier", vbTextCompare) Then
            Cells(RowCount, 1) = WorksheetFunction.Clean(.cell(1, 2).Range.Text)
            Cells(RowCount, 2) = WorksheetFunction.Clean(.cell(2, 2).Range.Text)
            Cells(RowCount, 3) = WorksheetFunction.Clean(.cell(10, 1).Range.Text)
            RowCount = RowCount + 1
        End If
    End With
Next iTable

Cheers,

Brendan

[gmaxey]

I thinkg the following will work provided the individual table cells do not contain a mix of checkbox and other formfield controls:

[vba]Cells(RowCount, 1) = WorksheetFunction.Clean(GetCellText(.cell(1, 2))[/vba]

Function CellGetText(ByRef oCell As Word.Cell) As String
    Dim oRng As Word.Range
    Dim strTemp As String
    Dim oChr As Range
    Dim lngIndex As Long
    Dim off As FormField
    lngIndex = 1
    Set oRng = oCell.Range
    oRng.MoveEnd wdCharacter, -1
    Debug.Print oRng.Text
    For Each oChr In oRng.Characters
        Select Case Asc(oChr)
            Case 21
                Set off = oRng.FormFields(lngIndex)
                If off.CheckBox.Value = True Then
                    strTemp = strTemp & "true"
                Else
                    strTemp = strTemp & "false"
                End If
                lngIndex = lngIndex + 1
            Case Else
                strTemp = strTemp & oChr
        End Select
    Next oChr
    CellGetText = strTemp
End Function

[bren_c]

Hi Greg,

I had a mixture of strings and checkboxes in each table cell, and your solution outputted each string and checkbox value in the correct order. This is even better than I was aiming for..

Thank you! That saved me a lot of hassle!

[gmaxey]

bren_c

You're welcome. I suppose with enough effort, I might be able to come up with code that could work with a mix of checkboxes and other formfield controls, but I just didn't have time to work it out.

[fumei]

"I might be able to come up with code that could work with a mix of checkboxes and other formfield controls"

Test for the .Type.

TextInput formfield = 70
Checkbox formfield = 71
Dropdown formfield = 83

[gmaxey]

Gerry,
I didn't have time to work it all out yesterday. I think this is probably better all around:

Sub Test()
    Dim oTbl As Word.Table
    Dim oCell As Word.Cell
    Set oTbl = Selection.Tables(1)
    For Each oCell In oTbl.Range.Cells
        MsgBox CellGetText(oCell)
    Next oCell
End Sub

Function CellGetText(ByRef oCell As Word.Cell) As String
    Dim oRng As Word.Range
    Dim oFF As FormField
    Dim strTemp As String
    Dim lngIndex As Long
    lngIndex = 1
    Set oRng = oCell.Range
    ' Clip end of cell marker
    oRng.MoveEnd wdCharacter, -1
    ' Formfield dropdownlist and checkboxes are represented in the text string by Chr(21)
    strTemp = oRng.Text
    ' Formfield dropdownlist and checkboxes are represented in the text string by Chr(21),
    ' formfield text is represented by the actual text.
    ' Accordingly, we have to convert the Chr(21) representing the checkboxes and dropdowns to a text value:
    For lngIndex = 1 To oRng.FormFields.Count
        Set oFF = oRng.FormFields(lngIndex)
        Select Case oFF.Type
            Case 70
                'Do nothing
            Case 71
                If oFF.CheckBox.Value = True Then
                    strTemp = Replace(strTemp, Chr(21), "true", , 1)
                Else
                    strTemp = Replace(strTemp, Chr(21), "false", , 1)
                End If
            Case 83
                 strTemp = Replace(strTemp, Chr(21), oFF.DropDown.ListEntries(oFF.DropDown.Value).Name, , 1)
        End Select
    Next lngIndex
    CellGetText = strTemp
End Function