View Full Version : extracting checkbox data from a MS Word table
bren_c
11-09-2012, 04:02 AM
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
11-09-2012, 04:39 AM
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
11-09-2012, 04:58 AM
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
11-09-2012, 06:04 AM
I thinkg the following will work provided the individual table cells do not contain a mix of checkbox and other formfield controls:
Cells(RowCount, 1) = WorksheetFunction.Clean(GetCellText(.cell(1, 2))
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
11-09-2012, 07:11 AM
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
11-09-2012, 07:45 AM
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
11-09-2012, 03:00 PM
"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
11-10-2012, 11:20 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.