Help getting Word content control text to populate specific cells in Excel

[EVilli]

I have a word form that has about 50 content controls on it, mostly rich text controls and some checkboxes. As well as an excel workbook that is formatted with 20+ worksheets.

I'm trying to get some of the typed information from Word to export into excel in different specific spots on one worksheet in the book.

The Word document is used as a questionnaire for me to fill out my area of the workbook so I use a fresh one of each every time there's a new client.

I've spent the past 3 days looking for information and ended up with a somewhat working code. I have an issue with some information not populating in the excel document that I can't figure out. I also removed a lot of the-
variables in the middle because it would be redundant.

I know this code is ugly but it was the best I could do :/


Sub Click_to_Export_Click()

On Error Resume Next
'this error message is so the other cells will populate


Dim WD As Document
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook


Set xlApp = Excel.Application
Set WD = ActiveDocument


xlApp.Visible = True


choice = Application.FileDialog(msoFileDialogOpen).Show


If choice <> 0 Then


strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)


Set xlBook = xlApp.Workbooks.Open(strPath)


End If


'First half is to select the excel file to use




Dim propName As Object
Set propName = ActiveDocument.Bookmarks("i_prop_name").Range
propName.Copy

xlBook.Sheets("Input").[i_prop_name].PasteSpecial Excel.xlPasteValues

Dim propAddress As Object
Set propAddress = ActiveDocument.Bookmarks("i_prop_Address").Range
propAddress.Copy

xlBook.Sheets("Input").[i_prop_address].PasteSpecial Excel.xlPasteValues


-------------------------Removed 6 other bookmarks here---------------------------------

Dim Orig As Object
Set Orig = ActiveDocument.Bookmarks("i_team_originator").Range
Orig.Copy

xlBook.Sheets("Input").[i_team_originator].PasteSpecial Excel.xlPasteValues




'this error message is to inform that manual entry is needed

If Err.Number <> 0 Then
MsgBox "It looks like one or more fields was not properly transfered. Please enter Manually" & vbCrLf & "Error - " & Err.Description
End If


End Sub

I know there's probably an easier way to loop and search for the info but I just started learning VBA on Monday and haven't gotten used to loops.

Any help would be greatly appreciated, Thanks!

p.s. I have all of this for a button if that makes any difference.

[macropod]

See, for example: [SOLVED] Extracting Word form Data and exporting to Excel spreadsheet (vbaexpress.com) (http://www.vbaexpress.com/forum/showthread.php?40406-Extracting-Word-form-Data-and-exporting-to-Excel-spreadsheet&p=291047&viewfull=1#post291047)
The code there is designed to be run from Excel, harvesting data from a folder of Word documents. If you're going to update Excel from Word, though, you need to address issues such as what happens if someone edits the data after they have been exported. With the Excel macro, all records can be overwritten by the simple expedient of changing:
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
to:
i = 0
or, if you want a header row:
i = 1

Instead of letting the code choose the columns for you, you could set the j value via a test of the content control's title and/or tag.

[EVilli]

Hi Paul,

Thanks for the response! That thread is actually where I began my research, albeit with a lot less knowledge than now, and I didn't know how to get the code to populate where I wanted it.

Some questions I have:
-I ran your base code (from your mentioned thread) just to see what it would return and nothing populated the excel cells no errors showing.

-For the below quote, is it possible to loop what I want if the destination cells aren't in sequence?

If you initially put just one document containing meaningful data in all of the content controls into the 'source' folder, the macro will go through through the content controls and output the data into the worksheet. you should then be able to marry-up the output with whatever column headings you'll want in the workbook.
-Would I be able to marry the output up to specific cell names instead?

-I understand the code for opening the file and the structure of the sequence. But, I can't wrap my head around what code is saying to find the text within the content controls , and more importantly: the code that instructs to transfer it to excel

Thank you again for taking the time to respond. I've been having a great, but also frustrating time trying to learn VBA. Your help has given me a second wind in motivation.

p.s. I'm including an example worksheet and the forum that I need to transfer data from to give more context.

[macropod]

-Would I be able to marry the output up to specific cell names instead?
As I said in my previous reply:

Instead of letting the code choose the columns for you, you could set the j value via a test of the content control's title and/or tag.

-I understand the code for opening the file and the structure of the sequence. But, I can't wrap my head around what code is saying to find the text within the content controls , and more importantly: the code that instructs to transfer it to excel
It simply reads the content controls directly and outputs the data to Excel via, via the single line:

With CCtrl
Select Case .Type
Case Is = wdContentControlCheckBox
j = j + 1
WkSht.Cells(i, j).Value = .Checked
Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
j = j + 1
WkSht.Cells(i, j).Value = .Range.Text
Case Else
End Select
End With
In the above code, i is the row reference and j is the column reference.