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).:banghead:

[macropod]

Presumably you're referring to the code such as in post #4 of:
http://www.vbaexpress.com/forum/showthread.php?40406-Extracting-Word-form-Data-and-exporting-to-Excel-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. :hi:

[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.