mhigh
06-12-2017, 11:45 AM
Hello everyone,
I am working on a way that we can get word forms to that employees fill out to be extracted to excel. So far i have VBA code that will search through a specific folder of word forms and get the data to an excel spreadsheet.
I am in need of a way to get it to update the data when i click my excel spreadsheet button with the code in it and not replace it each time. As well as get the code i have right now to look for a specific folder and not just make my manually do it. I will post the code below.
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 FmFld As Word.FormField
Dim strFolder As String, strFile As String
Dim WkSht As Worksheet, i As Long, j As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set WkSht = ActiveSheet
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(strFolder & "\*.doc", 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
For Each FmFld In .FormFields
j = j + 1
' WkSht.Cells(i, j) = CCtrl.Range.Text
WkSht.Cells(i, j) = FmFld.Result
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
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function
Any help would be much appreciated! Thanks!
I am working on a way that we can get word forms to that employees fill out to be extracted to excel. So far i have VBA code that will search through a specific folder of word forms and get the data to an excel spreadsheet.
I am in need of a way to get it to update the data when i click my excel spreadsheet button with the code in it and not replace it each time. As well as get the code i have right now to look for a specific folder and not just make my manually do it. I will post the code below.
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 FmFld As Word.FormField
Dim strFolder As String, strFile As String
Dim WkSht As Worksheet, i As Long, j As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set WkSht = ActiveSheet
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(strFolder & "\*.doc", 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
For Each FmFld In .FormFields
j = j + 1
' WkSht.Cells(i, j) = CCtrl.Range.Text
WkSht.Cells(i, j) = FmFld.Result
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
Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function
Any help would be much appreciated! Thanks!