Hi
I have been using the forums on this site for some time and have found them incredibly useful. Thank you!
I have a large number of word forms with content controls in paragraphs of text as well as tables. I also have an excel sheet which pulls the values in the word form fields, in order from left to right, in to the excel sheet. I have put titles in the excel sheet so that the values that are pulled in to the excel sheet fall under the respective titles of the values that they represent in the word form.
Here is a copy of my code:
Sub GetFormData()
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 = 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
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
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
Public Sub ClearSheet()
'Clear Sheet Data'
Rows("6:2000").Select
Selection.ClearContents
End Sub
The problem that I'm having is that users of the forms cannot delete any of the form fields in the word forms. If this occurs, the excel does not account for the deleted form field. Is there any way that I can code something which will leave a blank cell in excel where the form field was deleted? Currently, when a form field is deleted in the word document, the values in the excel sheet don't match up with the titles correctly.
Any help would be very appreciated.
Thanks!