I tried to search for this in the forum but could not find anything that helped me solve this myself.
I have a Word Document form with Rich Content Control fill-able areas, and a Excel Log used to track those forms.
The log has a VBA button to hyperlink the forms based on file name, now I would like a VBA button to be able to select the information needed from the forms and copy it into the Excel Log.
I have been experimenting with this code:
The issues:
1) it copies all the files in the folder, when i just want to select one at a time (if possible using ...\Service Request Forms\Service Request " + ActiveCell.FormulaR1C1 + ".docx")
2)I'm not entirely sure how to adjust the positioning of the Paste. I would like it to be just right of the currently selected cell.
3)If possible at all i would like to be able to specifically choose what content gets copied over (ex: textbox1, textbox2, textbox 5, textbox 9 etc. etc.)
I'm sorry if i'm bad at explaining myself. Any help would be greatly appreciated.
I have a Word Document form with Rich Content Control fill-able areas, and a Excel Log used to track those forms.
The log has a VBA button to hyperlink the forms based on file name, now I would like a VBA button to be able to select the information needed from the forms and copy it into the Excel Log.
I have been experimenting with this code:
Code:
Sub GetFormData() 'Note: this code requires a reference to the Word object model
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 = "\\sfile2\Mgmt_Svcs\Inventory\Inventory Control Unit\Portable Equipment\Service Calls\SERVICE REQUESTS 2017-2018\Service Request Forms"
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
j = 0
For Each CCtrl In .ContentControls
j = j + 1
WkSht.Cells(i, j) = CCtrl.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
The issues:
1) it copies all the files in the folder, when i just want to select one at a time (if possible using ...\Service Request Forms\Service Request " + ActiveCell.FormulaR1C1 + ".docx")
2)I'm not entirely sure how to adjust the positioning of the Paste. I would like it to be just right of the currently selected cell.
3)If possible at all i would like to be able to specifically choose what content gets copied over (ex: textbox1, textbox2, textbox 5, textbox 9 etc. etc.)
I'm sorry if i'm bad at explaining myself. Any help would be greatly appreciated.