Get Data Word to Excel: Select Specific Content Controls to Extract

[jnix612]

I located the VBA code on this site to extract data from several Word documents into Excel. IT IS AWESOME AND WORKS PERFECT. Thank you. My Word document is an application. We will receive 1,100 applications and this VBA code will be a huge time saver. But the Word document is 3 pages long and I only need specific information from Page 1 extracted. Is there a way to tell Excel to extract specific content controls? As it is written now the code will pull directly from ALL Content Controls, but I don't need all that info extracted. Worse case I can simply delete the columns in Excel because they will be easy to identify, but trying to automate as much as possible (this organization will never get a real paper processing database, this is a gov't agency and too many federal laws).

[macropod]

Presumably you're referring to the code such as in post #4 of:
http://www.vbaexpress.com/forum/show...el-spreadsheet
To make that code extract data from, say, the first 10 content controls, you could change:

           j = 0 
            For Each CCtrl In .ContentControls 
                j = j + 1 
                WkSht.Cells(i, j) = CCtrl.Range.Text 
            Next

to:

            For j = 1 to 10
                WkSht.Cells(i, j) = .ContentControls(j).Range.Text 
            Next

[jnix612]

GREAT! Thank you. I just tried and it works great.

[jnix612]

I have another question: Regarding the same code above. This code is working wonderful and believe me it is saving me HOURS, DAYS, MONTHS of data entry time. Well not me, I'm trying to help them come to at least the 20th century (yup not ready for the 21st). I have recommended they contract with an Excel expert because there is so much automation they can do with Excel to help them get by since the company can't purchase a functional database. They don't have a clue the power these simple software hold.

My Question: I would like the code to skip Column A. Start entering the data from the Word Content Controls into Column B. I have pasted the code below. If someone can tell me what to add that would be great.

Sub GetFormData()
     'Note: this code requires a reference to the Word object model
     'To do this, go to Tools|References in the VBE, then scroll down to the Microsoft Word entry and check it.
    Application.ScreenUpdating = False
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Dim CCtrl As Word.ContentControl
    Dim strFolder As String, strFile As String
    Dim WkSht As Worksheet, i As Long, j As Long
    strFolder = GetFolder
    If strFolder = "" Then Exit Sub
    Set WkSht = ActiveSheet
    i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
    strFile = Dir(strFolder & "\*.docx", vbNormal)
    While strFile <> ""
        i = i + 1
        Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
        With wdDoc
            For j = 1 To 27
                WkSht.Cells(i, j) = .ContentControls(j).Range.Text
            Next
        End With
        wdDoc.Close SaveChanges:=False
        strFile = Dir()
    Wend
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
    Application.ScreenUpdating = True
End Sub
 
Function GetFolder() As String
    Dim oFolder As Object
    GetFolder = ""
    Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
    If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
    Set oFolder = Nothing
End Function

[macropod]

Simply change:
WkSht.Cells(i, j) = .ContentControls(j).Range.Text
to:
WkSht.Cells(i, j + 1) = .ContentControls(j).Range.Text

PS: When posting blocks of code, please use the code tags, indicated by the# button on the posting menu. Without them, your code loses much of whatever structure it had.

[jnix612]

booo-yaaaa. Thank you so much. You have no idea how much I appreciate expert help. I'm not at all a coder, but I know how to follow instructions and experts like you share your knowledge. I make sure they know I did not write these codes, but I know how Excel work and I know how to research solutions.

NOTED: on how to post blocks of code. Thank you.