How do I write Microsoft Word Content Control field data to a text file using VBA?

Question

I have a seemingly trivial VBA task. However, I do not normally use VBA and almost never in the context of Microsoft Word. I have a Word form that utilizes Content Control fields (as well as a few ActiveX radio buttons) and need to test it by printing a time stamp and completed form entries to a comma delimited file. When I run the following code:

Sub WriteToText()
Dim DataFile As String
Dim StrData As String
Dim CCtrl As ContentControl
Dim bControl_Exists As Boolean

DataFile = "C:\Users\Annabanana\Documents\Data.txt"
StrData = "": Open DataFile For Append As #1
StrData = Format(Now, "DD-MMM-YYYY hh:mm:ss")

With Application.ActiveDocument
    bControl_Exists = .Saved
    For Each CCtrl In ThisDocument.ContentControls
        With CCtrl
            Select Case .Type
                Case Is = wdContentControlCheckBox
                    StrData = StrData & "," & .Checked
                Case wdContentControlDate, wdContentControlDropdownList, wdContentControlComboBox, wdContentControlText
                    StrData = StrData & "," & .Range.Text
                Case Else
            End Select
        End With
    Next
End With
Print #1, StrData: Close #1
End Sub

I get the datestamp but nothing else. Ideally I would like to eventually print field tags and their corresponding values as such:

Tag1 Value1 Tag2 Value2 Tag3 Value3 .............

Eventually all of those would print to a database, but at this point I just want to be able to see how the data comes out of the form and how I need to transform it prior to loading. Any advice is greatly appreciated. Thank you.

macropod macropod · Accepted Answer · 2020-05-20T22:50:19

At its heart, you problem comes down to the misuse of ThisDocument. Try:

Sub GetCCtrlData()
Dim CCtrl As ContentControl, StrData As String, DataFile As String
StrData = Format(Now, "DD-MMM-YYYY hh:mm:ss")
DataFile = "C:\Users\" & Environ("UserName") & "\Documents\Data.txt"
For Each CCtrl In ActiveDocument.ContentControls
  With CCtrl
    StrData = StrData & vbTab & .Title & "|" & .Tag & ": "
    Select Case .Type
      Case Is = wdContentControlCheckBox
        StrData = StrData & .Checked
      Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
        StrData = StrData & .Range.Text
      Case Else
    End Select
  End With
Next
Open DataFile For Append As #1: Print #1, StrData: Close #1
End Sub

The above code also exports the content control titles and tags, using tab-delimiters instead of commas. Using tab-delimiters allows for the possibility there might be commas in the data.

At some stage, I imagine, you'll want to process multiple documents. For that, see: 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 extracts the data to an Excel workbook, the underlying principles are the same.

How do I write Microsoft Word Content Control field data to a text file using VBA?

1 Answers