Word Content Control Data Import to Excel Table

[jewel]

A previous post, Extracting Word form Data and exporting to Excel spreadsheet, provides the solution to open all the Word files in a folder and copy all the content control data to Excel. This is very close to what I would like to accomplish, but I would like the resulting data to be placed in a named Excel table.

I used Macropod's code as a starting point. Attempted to modify the wdDoc portion of the code to reference the Excel table (tbl_Employees) on the Employees worksheet, but hit a snag.

The code is in the Employees.xlsm file. Example files for extraction are the two *.docx files.

The current code is (sadly) broken.
I appreciate any help someone might offer.
Thanks in advance,
Jewel

[macropod]

Presumably you're referring to: http://www.vbaexpress.com/forum/showthread.php?40406-Extracting-Word-form-Data-and-exporting-to-Excel-spreadsheet&p=257696&viewfull=1#post257696
If you're wanting to send the data to a specific worksheet, change:
Set WkSht = ActiveSheet
to, for example:
Set WkSht = Sheets("Employees")
If you want the data to start populating in Column B instead of Column A, change:
j = 0
to:
j = 1

[jewel]

Thanks for your reply, Paul, and all the great guidance you have provided in this forum. :bow:

I actually want the data to land in the named Excel table, tbl_Employees by adding a new row to the existing table.

I was trying to blend your approach of capturing the CC data (very elegant!) and Susan Hardkins' How to update UserForm VBA code to accommodate an Excel Table to pull the CC data into an Excel table (for some reason the forum is denying a posting if I add a hyperlink, apologies).

She sets a variable oNewRow as follows: Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
She further explains... In this case, I believe the oNewRow variable is more efficient because one statement inserts the input values as a new record and tells Excel where the values go.

Using her syntax of ListRows.Add you would not have to locate the last filled cell (it seems to me).
So this was one and there are many other reasons for wanting to use an Excel table.

It seems to me that I am trying to address Excel in the Word portion of the macro. So maybe that is really my question?

[macropod]

Presumably the Hadkins page is: http://www.techrepublic.com/article/office-q-a-how-to-update-userform-vba-code-to-accommodate-an-excel-table/

To use the named Excel table approach, you might do something along the lines of (untested):

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 strFolder As String, strFile As String
Dim WkSht As Worksheet, xlRw As ListRow, c As Long
strFolder = GetFolder: If strFolder = "" Then Exit Sub
Dim wdApp As New Word.Application, wdDoc As Word.Document, CCtrl As Word.ContentControl
Set WkSht = ActiveSheet
strFile = Dir(strFolder & "\*.docx", vbNormal)
While strFile <> ""
Set wdDoc = wdApp.Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
Set xlRw = WkSht.ListObjects("tbl_Employees").ListRows.Add(AlwaysInsert:=True)
With wdDoc
c = 0
For Each CCtrl In .ContentControls
c = c + 1
With CCtrl
Select Case .Type
Case Is = wdContentControlCheckBox
xlRw.Range.Cell(c) = .Checked
Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
If IsNumeric(.Range.Text) Then
If Len(.Range.Text) > 15 Then
xlRw.Range.Cell(c) = "'" & .Range.Text
Else
xlRw.Range.Cell(c) = .Range.Text
End If
Else
xlRw.Range.Cell(c) = .Range.Text
End If
Case Else
End Select
End With
Next
End With
wdDoc.Close SaveChanges:=False
strFile = Dir()
Wend
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing: Set xlRw = 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

[jewel]

Hi Paul. Thanks again.
However, I get the following error message: Run-time error 438, Object doesn't support this property or method. For this line: xlRw.Range.Cell(c) = CCtrl.Range.Text

The Watch reports: Watch : : Range : <Expression not defined in context> : Empty :

Any ideas? Or point me to the documentation for this topic?

[jewel]

Ok, now having another issue. In this portion of the code:

Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
It waits endlessly for Word to do it's thing. I have to close Word using the Task Manager and then kill the Word process to clear this problem. The error message is "Microsoft Excel is waiting for another application to complete an OLE action."

Also, in reference to my last comment, when I entered the line with "xlRow.Range." the intelliprompt offered Cells, but not Cell, so I changed that to:

xlRw.Range.Cells(c) = CCtrl.Range.Text
but of course it never quite makes it to that line.

[jewel]

OK, one last thing, I thought maybe Word was left in some indeterminate state, so I rebooted, and tried to open the input Word files individually. One was corrupted so I deleted that one and tried again. But no avail. Same error waiting for OLE action.

[macropod]

There is no reason I can see for the code to fail at:

Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
Maybe you have a faulty Office installation. Try repairing it (via Windows Control Panel > Programs > Programs & Features > Microsoft Office (version) > Change > Repair).

[jewel]

I repaired Office as you suggested, and now this works. Thank you, thank you for all your guidance! :yes

[jewel]

One note, the references article is by Susan Harkins; sorry for misspelling your name Susan! Thanks for your help, too.

[PA1]

Hi Mr.paul
From the previous post of

Word Content Control Data Import to Excel Table

Getformdata() function

i have a small query

how can i modify code to import CHEK BOX contents
such as
selecting genders
when i click male the excel cell should get the male text.
thanks

[macropod]

I have modified the code to output content control checkbox status and for enhanced numeric data handling. There is no way for the code to know what a particular checkbox's state indicates without adding further logic and testing.

[PA1]

I have added a form Check box.

[macropod]

Meaning what, exactly?

[PA1]

Meaning what, exactly?

22280

i have created template with these settings.
i have many files with different data.
i need to export these data to excel file.
i have used Getformdata() function.
i am getting all the data except the Two checkbox data.
thank you.

[macropod]

I cannot tell from you image what those boxes are. For all I know they could be Wingding boxes. The macro works with content controls only; if your checkboxes are not content control checkboxes, the code won't process them.

[Tolkien]

As a result, did you manage to optimize the code so that everything worked as it should? I wouldn't mind learning how to import data from a word document into an excel spreadsheet. However, I don't use word documents as often as, for example, various table editors. I also wanted to ask if it is possible to change your program code to import the data from excel to Google Sheets? I know it sounds a lot simpler than it probably is, but I'm still interested to find out. I'm not a programmer and don't really understand programming languages, but I still hope you can explain more details (https://www.coupler.io/google-sheets-integrations/microsoft-excel-to-google-sheets) about your code and how I can use it as a simple user.