以Excel的VBA進行word表單蒐集

很多人都知道 word可以進行表單的設計，但是很少人知道如何去收集表單上的資料，所以查了一下網絡上，然後改寫程式，主要除了判斷一般的文字外，尚可將表單中的圖片進行蒐集到Excel中。

執行該程式前，必須於VBA中設定引用項目。

於VBA中設定引用項目Word物件

以下為VBA程式:

Sub GetFormData()
‘Note: this code requires a reference to the Word object model
Application.ScreenUpdating = False
Dim wdApp As New Word.Application, wdDoc As Word.Document, CCtrl As Word.ContentControl
Dim strFolder As String, strFile As String, 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 & “*.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
With CCtrl
Select Case .Type
Case Is = wdContentControlCheckBox
j = j + 1
WkSht.Cells(i, j).Value = .Checked
Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
j = j + 1
WkSht.Cells(i, j).Value = .Range.Text
Case Is = wdContentControlPicture
j = j + 1
WkSht.Cells(i, j).Value = .Range.Text
WkSht.Cells(i, j).Left = 100

       Case Else
    End Select
  End With
Next
.Close SaveChanges:=False

End With
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