Printable View
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
What kind of option button, the wdFieldFormCheckBox? Maybe you could attach a short example DOC.
[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
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.
Many thanks Kenneth
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]
Hi Kenneth,
it's working wonderfully...you're a king !!! :beerchug:
many thanks to you
Cheers
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?
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.
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.
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.
If Word is like Excel, the macro recorder doesn't do much in 2007.
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]
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.
Here is a sample .doc file.
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
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)?
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]
Golly, just when I thought ribbons should be on Christmas/Birthday presents, another selling point...Quote:
Originally Posted by xld
Worked fine from an xlsx for me :(Quote:
Originally Posted by spalmgren