Solved: Get MS Word OptionButton value from Excel VBA

[rico99]

Hi all,

I've create a MS Word document which contain an OptionButton, i want to retrieve the value (checked or unchecked) from an Excel macro.

The goal is to collect answers from many filled word documents, and put it into an Excel Spreadsheet.

Many thanks for your help

[Kenneth Hobs]

What kind of option button, the wdFieldFormCheckBox? Maybe you could attach a short example DOC.

[rico99]

[IMG]file:///C:/DOCUME%7E1/ERIC%7E1.MAL/LOCALS%7E1/Temp/moz-screenshot-1.jpg[/IMG]Hi Kenneth,

It's not a form, i've put the screenshot of toolbar and the composant for which i want to retrieve value from Excel macro ... hope you can see it, it will be a bit complicate to upload it

Many thanks for your help

[Kenneth Hobs]

Good example. It is from the Control Toolbox toolbar I see. I like those better.

Since you can't post a sample doc, I will create a simple one to test. I will look into the matter later today and post back.

[rico99]

Many thanks Kenneth

[Kenneth Hobs]

I used both early and late binding so you will need to set the MSWord reference. You will need to change the Inputs wordFilename and startColumnName to fit your needs.

[vba]
Sub MSWordOptionButtionInfo()
'Requires reference: MSWord 11.0 Object Library
Dim oShape As Word.InlineShape
Dim wdApp As Object, wd As Object, rn As Long
Dim wordFilename As String, startColumnName As String
Dim r As Range, counter As Integer

'Inputs
wordFilename = "x:\MSWord\OptionButtons.doc"
startColumnName = "A"

'Exit if word file does not exist
If Dir(wordFilename) = "" Then Exit Sub

'set wdApp reference
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

'Set DOC file with option buttons to open
Set wd = wdApp.Documents.Open(wordFilename)
wdApp.Visible = False

'Set Initial Range and counter
Set r = Range(startColumnName & Rows.count).End(xlUp).Offset(1, 0)
counter = 0

'Put option button name at in of startColumnName and value in cell to the right
For Each oShape In ActiveDocument.InlineShapes
If oShape.OLEFormat.progID = "Forms.OptionButton.1" Then
r.Offset(counter, 0).Value = oShape.OLEFormat.Object.Name
r.Offset(counter, 1).Value = oShape.OLEFormat.Object.Value
counter = counter + 1
End If
Next oShape

Set wd = Nothing
Set wdApp = Nothing
End Sub[/vba]

[rico99]

Hi Kenneth,

it's working wonderfully...you're a king !!!

many thanks to you

Cheers

[spalmgren]

I am facing the same situation with the exception that I have used ActiveX controls in my word document.

The macro you wrote doesn't seem to work for me. It gives the error message "Object variable not set".

What do I need to change in the above code to make it work?

[Kenneth Hobs]

Use debug to see what object is causing the problem.

I wrote that code for MSOffice 2003. If you are using in in 2007 or 2010, there might have been some change.

The option button that it checks is from the Control Toolbox.

[spalmgren]

Yeah that's correct, I am using 2007. Do you know what could have changed?

From the debug it seems like it is the "oShape" causing the problem.

[Kenneth Hobs]

I won't get 2007 for a few weeks so I can't test. I will get 2010 in a day or so. Until then, you could record a macro in MSWord where you add the control (option button) and post it here. I can then see what was changed possibly.

[Bob Phillips]

If Word is like Excel, the macro recorder doesn't do much in 2007.

[spalmgren]

In 2007 it looks like this when I add an ActiveX Option button.
[VBA]Sub AddButton()
'
' AddButton Macro
'
'
ActiveDocument.ToggleFormsDesign
Selection.InlineShapes.AddOLEControl ClassType:="Forms.OptionButton.1"
End Sub[/VBA]

[Kenneth Hobs]

It appears to be the same. At this point, you might post a sample DOCX. I would have to convert it to a DOC to test. I don't expect to find a problem though.

[spalmgren]

Here is a sample .doc file.

[Kenneth Hobs]

In Excel 2003, I got:
OptionButton2 FALSE

OptionButton21 FALSE

OptionButton22 FALSE

OptionButton1 FALSE

OptionButton11 FALSE

OptionButton12 TRUE

OptionButton13 FALSE

OptionButton14 FALSE

OptionButton16 FALSE

OptionButton17 FALSE

[spalmgren]

Yeah, when I run it from a .xls of file (instead .xlsx) it works!

Thanks a lot Kenneth! You've saved me a lot of trouble!

Just a short follow-up question; is there an easy way to adapt the script to include all controls, not only option buttons (but also check boxes and text boxes)?

[Kenneth Hobs]

Yes, leave out the IF and End If lines in the For loop.

In the debug commented line, notice how one can check for a control type in another way. One could write the code to just iterate certain types of controls with IF's or a Select Case.
[vba]Sub MSWordOptionButtionInfo()
'Requires reference: MSWord 11.0 Object Library
Dim oShape As Word.InlineShape
Dim wdApp As Object, wd As Object, rn As Long
Dim wordFilename As String, startColumnName As String
Dim r As Range, counter As Integer

'Inputs
'wordFilename = "x:\MSWord\OptionButtons.doc"
wordFilename = "x:\MSWord\a.doc"
startColumnName = "A"

'Exit if word file does not exist
If Dir(wordFilename) = "" Then Exit Sub

'set wdApp reference
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0

'Set DOC file with option buttons to open
Set wd = wdApp.Documents.Open(wordFilename)
wdApp.Visible = False

'Set Initial Range and counter
Set r = Range(startColumnName & Rows.Count).End(xlUp).Offset(1, 0)
counter = 0

'Put option button name at in of startColumnName and value in cell to the right
For Each oShape In wdApp.ActiveDocument.InlineShapes
'Debug.Print oShape.OLEFormat.ClassType 'Forms.OptionButton.1
'If oShape.OLEFormat.progID = "Forms.OptionButton.1" Then
'Debug.Print Split(oShape.OLEFormat.ClassType, ".")(1) 'OptionButton
If Split(oShape.OLEFormat.ClassType, ".")(1) = "OptionButton" Then
r.Offset(counter, 0).Value = oShape.OLEFormat.Object.Name
r.Offset(counter, 1).Value = oShape.OLEFormat.Object.Value
counter = counter + 1
End If
Next oShape

Set wd = Nothing
Set wdApp = Nothing
End Sub[/vba]

[GTO]

If Word is like Excel, the macro recorder doesn't do much in 2007.

Golly, just when I thought ribbons should be on Christmas/Birthday presents, another selling point...

[Bob Phillips]

Yeah, when I run it from a .xls of file (instead .xlsx) it works!

Thanks a lot Kenneth! You've saved me a lot of trouble!

Just a short follow-up question; is there an easy way to adapt the script to include all controls, not only option buttons (but also check boxes and text boxes)?

Worked fine from an xlsx for me