Sub GetSermonData()
'Note: this code requires a reference to the Word object model.
'See under the VBE's Tools|References.
Application.ScreenUpdating = False
Dim wdApp As New Word.Application, wdDoc As Word.Document, StrTxt As String
Dim strFolder As String, strFile As String, WkSht As Worksheet, r As Long
strFolder = GetFolder: If strFolder = "" Then GoTo ErrExit
Set WkSht = ActiveSheet
r = WkSht.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
With wdDoc
With .Range.Find
.Text = "(Date: [!^13]@[0-9](4))."
.Replacement.Text = "\1^pLocation:"
.Forward = True
.Format = False
.Wrap = wdFindContinue
.MatchWildcards = True
.Execute Replace:=wdReplaceOne
End With
StrTxt = .Range.Text: r = r + 1
.Close SaveChanges:=False
End With
On Error Resume Next
WkSht.Cells(r, 1).Value = Trim(Split(Split(StrTxt, "Series:")(1), "Title:")(0))
WkSht.Cells(r, 2).Value = Trim(Split(Split(StrTxt, "Title:")(1), vbCr)(0))
WkSht.Cells(r, 3).Value = Trim(Split(Split(StrTxt, "Main Idea:")(1), vbCr)(0))
WkSht.Cells(r, 4).Value = Trim(Split(Split(StrTxt, "Text:")(1), vbCr)(0))
WkSht.Cells(r, 5).Value = Trim(Split(Split(StrTxt, "Date:")(1), vbCr)(0))
WkSht.Cells(r, 6).Value = Trim(Split(Split(StrTxt, "Location:")(1), ",")(0))
On Error GoTo 0
strFile = Dir()
Wend
ErrExit:
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