beila00
02-22-2016, 11:28 AM
Hi there, I watched a video on youtube to get data from Word forms in Excel using vba. My word document has formfields that contain the data I want, and I only want two of the fields, not all of them. I have tried to play with the code in the video but am stuck and was hoping someone can help. Here is what I originally started with: The code I wrote, in Excel, works but is currently only pulling from one file. I would like for it to pull every file that I have saved in a particular folder.
Sub wrd11()
Dim wrd As Word.Application
Set wrd = CreateObject("Word.Application")
With wrd.Documents.Open "C:\Users\dculnane\Documents\Performance Evaluations\Working forms\PE Manager Form.docx".Visible = True
End With
Range("a2").Value = wrd.ActiveDocument.FormFields("Text8").Result
Range("b2").Value = wrd.ActiveDocument.FormFields("Text20").Resultwrd.Quit
End Sub
I can’t figure out where to change my code. this works, but only pulls from one file. I need to use formfields and not content controls.Here is what I have so far:
Sub getWordFormData()
Dim wdApp As New Word.Application
Dim myDoc As Word.Document
Dim FFtl As Word.FormFields
Dim myFolder As String, strFile As StringDim myWkSht As Worksheet, i As Long, j As Long
myFolder = "C:\temp\test"
Application.ScreenUpdating = False
If myFolder = "" Then
Exit Sub
Set myWkSht = ActiveSheet
ActiveSheet.Cells.Clear
Range("A1") = "Employee Name"
Range("A1").Font.Bold = True
Range("B1") = "Total"
Range("B1").Font.Bold = True
i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(myFolder & "\*.docx", vbNormal)
While strFile <> ""i = i + 1
Set myDoc = wdApp.Documents.Open(Filename:=myFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
With myDocj = 0
For Each FFtl In .FormFieldsj = j + 1
myWkSht.Cells(i, j) = FFtl.Range.Text
Next
myWkSht.Columns.AutoFit
End With
myDoc.Close SaveChanges:=Falsestr
File = Dir()WendwdApp.Quit
Set myDoc = Nothing:
Set wdApp = Nothing:
Set myWkSht = Nothing
Application.ScreenUpdating = True
End Sub
Sub wrd11()
Dim wrd As Word.Application
Set wrd = CreateObject("Word.Application")
With wrd.Documents.Open "C:\Users\dculnane\Documents\Performance Evaluations\Working forms\PE Manager Form.docx".Visible = True
End With
Range("a2").Value = wrd.ActiveDocument.FormFields("Text8").Result
Range("b2").Value = wrd.ActiveDocument.FormFields("Text20").Resultwrd.Quit
End Sub
I can’t figure out where to change my code. this works, but only pulls from one file. I need to use formfields and not content controls.Here is what I have so far:
Sub getWordFormData()
Dim wdApp As New Word.Application
Dim myDoc As Word.Document
Dim FFtl As Word.FormFields
Dim myFolder As String, strFile As StringDim myWkSht As Worksheet, i As Long, j As Long
myFolder = "C:\temp\test"
Application.ScreenUpdating = False
If myFolder = "" Then
Exit Sub
Set myWkSht = ActiveSheet
ActiveSheet.Cells.Clear
Range("A1") = "Employee Name"
Range("A1").Font.Bold = True
Range("B1") = "Total"
Range("B1").Font.Bold = True
i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(myFolder & "\*.docx", vbNormal)
While strFile <> ""i = i + 1
Set myDoc = wdApp.Documents.Open(Filename:=myFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
With myDocj = 0
For Each FFtl In .FormFieldsj = j + 1
myWkSht.Cells(i, j) = FFtl.Range.Text
Next
myWkSht.Columns.AutoFit
End With
myDoc.Close SaveChanges:=Falsestr
File = Dir()WendwdApp.Quit
Set myDoc = Nothing:
Set wdApp = Nothing:
Set myWkSht = Nothing
Application.ScreenUpdating = True
End Sub