Sending text from a form field in Word to Excel.

[rngd]

Hi everyone,

I am trying to create a word document where a button can be pressed and the text from 4 of the text form fields are sent to an excel sheet. (WorkOrder#, Description, Priority, Date.) Many people will be using this document and it will be made as a template but before that I need to finish all the coding. The location of the word docs and excel sheet are in the same folder on our network. I have had a little experience in VB GUI coding but not sure how this one works.
Each time the button is pressed I would like the text from the 4 fields saved on the next available line in the excel sheet. (note: I was able to figure out how to use the text from a form field using the bookmarks.)

Any help would be great,
Thanks
David

[rngd]

I was able to change up some code that I found in Youtube tutorial. For those interested or will have the same question in the future, here is the link: /watch?v=_NoQXYTLOEc

David

[gmaxey]

That is not a link.

[rngd]

Sorry, just add that to the end of youtube. com
I did not have enough posts to insert a link into my comments

David

[gmaxey]

David,

Thanks. I looked at the code in the video and while I am not too many steps from being newbie myself wrt to Excel programming, I would have done it a little differently:


Sub GetWordFormCCData()
Dim oApp As Object
Dim oDoc As Object
Dim oCC As Object
Dim strFolder As String, strFile As String
Dim oSheet As Worksheet, oRng As Range
Dim lngIndex As Long, lngCC As Long
strFolder = "D:\Forms"
If strFolder = "" Then Exit Sub
Set oApp = CreateObject("Word.Application")
Application.ScreenUpdating = False
Set oSheet = ActiveSheet
If MsgBox("Do you want to clear data and refresh sheet?" & vbCr + vbCr _
& "Note: Clicking ""No"" will append form data to end of sheet", vbYesNo, "Refresh/Append") = vbYes Then
oSheet.Cells.Clear
End If
On Error GoTo Err_Handler
strFile = Dir(strFolder & "\*.doc*", vbNormal)
While strFile <> ""
lngIndex = oSheet.Cells(oSheet.Rows.Count, 1).End(xlUp).Row
Set oDoc = oApp.Documents.Open(strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
If lngIndex = 1 Then
'Add column headings
For Each oCC In oDoc.ContentControls
lngCC = lngCC + 1
Set oRng = oSheet.Cells(lngIndex, lngCC)
With oRng
.Value = oCC.Title
.Font.Bold = True
End With
Next oCC
End If
lngIndex = lngIndex + 1
lngCC = 0
With oDoc
For Each oCC In .ContentControls
lngCC = lngCC + 1
oSheet.Cells(lngIndex, lngCC) = oCC.Range.Text
Next
oSheet.Columns.AutoFit
.Close SaveChanges:=False
End With
strFile = Dir()
Wend
Application.ScreenUpdating = True
lbl_Exit:
oApp.Quit
Set oDoc = Nothing
Set oApp = Nothing
Set oSheet = Nothing
Exit Sub
Err_Handler:
Resume lbl_Exit
End Sub


You might find this interesting: http://gregmaxey.mvps.org/word_tip_pages/extract_data_from_forms.html