VBA Pull Information from a Word Form into Excel

Sanchez88

New Member
Joined
Oct 16, 2017
Messages
7
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:
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,223,626
Messages
6,173,415
Members
452,514
Latest member
cjkelly15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top