Need to edit VB code to transfer Word form data to Excel

[nikki2503]

Hi,

I've inherited some VB code that takes data from a word form and puts it in Excel. Unfortunately the code uses form fields and I want to be able to use content controls. Can someone tell me how to edit the code to use Content Controls?

The code I have is below

Dim vField As FormField
Dim fso As Scripting.FileSystemObject
Dim fsDir As Scripting.Folder
Dim fsFile As Scripting.File
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim vColumn As Integer
Dim vLastRow As Integer
Dim x As Integer

Sub AddFormFields()

vLastRow = ActiveSheet.UsedRange.Rows.Count + 1
vColumn = 1

Set fso = New Scripting.FileSystemObject

Set fsDir = fso.GetFolder _
("\\MEL19A001PPN\uam2a$\Evaluation\Evaluation Auto Form\DS Template\Unprocessed\")

Set wdApp = New Word.Application
wdApp.Visible = True
For Each fsFile In fsDir.Files
wdApp.Documents.Open (fsFile)
Set myDoc = wdApp.ActiveDocument
For Each vField In wdApp.Documents(myDoc).FormFields
vField.Select
vValue = vField.Result
Workbooks("TemplateDS.xls").Activate

Cells(vLastRow, vColumn).Select
If vField.Type = 71 Then
Select Case vField.Name
Case "Check1"
vColumn = vColumn - 1
If vField.Result = "1" Then
ActiveCell.Value = "YES"
End If
Case "Check2"
If vField.Result = "1" Then
ActiveCell.Value = "NO"
End If
End Select
Else
ActiveCell.Value = vValue
End If
vColumn = vColumn + 1
Next
vColumn = 1
vLastRow = vLastRow + 1
vFileName = wdApp.ActiveDocument.Name
wdApp.ActiveDocument.Close
Name fsFile As _
"\\MEL19A001PPN\uam2a$\Evaluation\Evaluation Auto Form\DS Template\Processed\" & vFileName

Next
wdApp.Quit
MsgBox "You have now uploaded unprocessed files."
End Sub


Thanks

[macropod]

See, for example: http://www.vbaexpress.com/forum/showthread.php?40406-Extracting-Word-form-Data-and-exporting-to-Excel-spreadsheet&p=257696&viewfull=1#post257696
Although the code there is written around the extraction of data from text-containing content controls (checkbox content controls are only supported from Word 2010 onwards), and checkbox content controls would return TRUE/FALSE instead of 1/0 or Yes/No, the code could easily be modified to do that, too.