Consulting

Results 1 to 8 of 8

Thread: Capture content controls to Excel, handle various control types

  1. #1

    Capture content controls to Excel, handle various control types

    Hi, I have an existing script that retrieves all Content Controls from a Word Document and prints it into an Excel spreadsheet.

    Currently it works for free text/rich text content control types but I need to extend this functionality to Checkbox types.

    Ideally it will capture "True" or "False" depending whether the checkbox is ticked or not

    Here is the existing code:

    Sub ContentControlExtraction()
        ' VARIABLES FOR THIS WORKBOOK
        Dim DocWorkbook As Workbook
        Set DocWorkbook = Application.ActiveWorkbook
        Dim DocSheet As Worksheet
        Set DocSheet = DocWorkbook.ActiveSheet
        DocSheet.Cells(1, 1).Value = "Field Name"
        DocSheet.Cells(1, 2).Value = "Value"
        
        ' OPEN WORD DOCUMENT
        Dim WordApp As Word.Application
        Set WordApp = CreateObject("Word.Application")
        WordApp.Visible = True
        Dim WordDoc As Word.Document
        Set WordDoc = WordApp.Documents.Open(ThisWorkbook.Path & "\test.docx")
        
        Dim intNumContentControls As Integer
        intNumContentControls = WordDoc.ContentControls.Count
          
        ' LOOP FOR EACH CONTENT CONTROL
        Dim i As Integer
        For i = 1 To intNumContentControls
            DocSheet.Cells(i + 1, 1).Value = WordDoc.ContentControls(i).Title
            DocSheet.Cells(i + 1, 2).Value = WordDoc.ContentControls(i).Range.Text
        Next i
        
        Debug.Print "NUMBER OF CONTENT CONTROLS: " & intNumContentControls
            
    End Sub
    How would you modify this code to work with Checkbox content controls?

    I.e. if it is free text/rich text, just capture as it is, if it is checkbox type, translate it to "True" or "False"

    Also, sometimes the form I am capturing uses Wingdings Character 254 (a tickbox) for Checked, instead of the standard 'X' box. Do take this into account

  2. #2
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    For i = 1 To intNumContentControls
          DocSheet.Cells(i + 1, 1).Value = WordDoc.ContentControls(i).Title
          If WordDoc.ContentControls(i).Type = 8 Then
            If WordDoc.ContentControls(i).Checked Then
              DocSheet.Cells(i + 1, 2).Value = "True"
            Else
              DocSheet.Cells(i + 1, 2).Value = "True"
            End If
          Else
            DocSheet.Cells(i + 1, 2).Value = WordDoc.ContentControls(i).Range.Text
          End If
        Next i
    Greg

    Visit my website: http://gregmaxey.com

  3. #3
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    This has been dealt with before. A simple forum search could have turned up:
    http://www.vbaexpress.com/forum/show...to-Excel-Table
    http://www.vbaexpress.com/forum/show...l=1#post257696
    and, further down in the same thread:
    http://www.vbaexpress.com/forum/show...l=1#post291047
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    I am pretty sure what you meant to say was : ----
    Quote Originally Posted by gmaxey View Post
            If WordDoc.ContentControls(i).Checked Then
              DocSheet.Cells(i + 1, 2).Value = "True"
            Else
              DocSheet.Cells(i + 1, 2).Value = "False"
            End If
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    Hi, I want to search WordDoc.ContentControls for the ContentControl that has Title of "CompanyName", then extract the Text from that ContentControl, how do I do this efficiently? I.e. no looping if possible

  6. #6
    Assuming the content control exists and there is only one CC with that title in the document. The value would be something like

    DocSheet.Cells(i + 1, 2).Value = WordDoc.SelectContentControlsByTitle("CompanyName").Item(1).Range.Text
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    I modified the code a bit

    For i = 1 To intNumContentControls       DocSheet.Cells(i + 1, 1).Value = WordDoc.ContentControls(i).Title
          If WordDoc.ContentControls(i).Type = 8 Then
            If WordDoc.ContentControls(i).Checked Then
              DocSheet.Cells(i + 1, 2).Value = "True"
            Else
              DocSheet.Cells(i + 1, 2).Value = "True"
            End If
          Else
            DocSheet.Cells(i + 1, 2).Value = WordDoc.ContentControls(i).Range.Text.Trim
          End If
        Next i
    Added a .Trim to Range.Text

    Now I get Invalid Qualifier error. How do I make this code work?

  8. #8
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    You can start by removing "Trim" after .Text. That is what caused the invalid qualifier. Do you want to "Trim" the content of the CC?

    Trim(WordDoc.ContentControls(i).Range.Text)

    Change the second "True" to "False" as Graham correctly points out.
    Greg

    Visit my website: http://gregmaxey.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •