Get MS Word ActiveX Control values from Excel VBA

[spalmgren]

Hi all,

I've created a MS Word document (a survey form) which contains several ActiveX Controls (Check boxes, Text boxes, Option buttons), and in order to analyze the forms I want to retrieve the values 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

[spalmgren]

I could also add that I have tried this code (from earlier post) unsuccessfully:

Sub MSWordButtonInfo()
'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 = "C:\A.docx"
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

[Bob Phillips]

Doesn't your code do what you want?

[Bob Phillips]

BTW, it has to be a macro enabled workbook if it has ActiveX controls in it.

[spalmgren]

Yeah the workbook is marco enabled, however the ActiveX controls are in the word document.

And no, it doesn't work. The debuger points to the oShape and says "Object variable not set".

[Bob Phillips]

Is the file a docm?

[spalmgren]

Yes it is a docm.

[Bob Phillips]

The script says docx!

[spalmgren]

Yeah true it does. Just a typo by me when I anonymized the script, it should be .docm

[Bob Phillips]

I tried it with docm and it worked fine or me in a doc with two option buttons.

[Bob Phillips]

I did change this when I used your doc



For Each oShape In wdApp.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