The Bible In Excel
John Walkenbach has the Bible in Excel — I've created a small Excel app that retrieves Bible verses from the web.
Download the Bible App — Excel 2003
Screenshot
Run the application
To run the app, just run the StartForm macro in the workbook. You'll need to select the book (either from the combo box or the list box), chapter and verse. The combo box and list box are interdependent; selecting from the combo box highlights the book name in the listbox, and selecting the book name from the listbox selects the appropriate book number from the combo box. Click Show to see the appropriate text.
You may also double-click the row in the list box, and the corresponding verse text will display below it.
Options
There are a few options you can control. The Cache Request checkbox lets you save the XML file for that particular Book-Chapter-Verse (BCV) combination. If you try to re-run the same exact query, the app re-uses the existing XML file rather than fetching the text from the web again. The web fetch is fast, but not as fast as using the saved XML file.
The Clear cache on exit checkbox will delete all XML files from your system temp folder. It blindly deletes any files with .xml extension. Go to the VB IDE Immediate Window and type ?Environ("temp") if you want to know where this folder is on a given computer.
Note that if you clear out your temp folder while the app is running, or in between sessions (with Clear cache on exit unchecked on exit), the XML files will be deleted by your system regardless of the app settings.
Here's the function that returns the appropriate Bible verse. It downloads the verse, creates a temporary XML file and parses it, then deletes the XML file.
The GetBibleVerse Function
verse As String) As String
' http://www.webservicex.net/WCF/ServiceDetails.aspx?SID=43
Dim xml As Object
Dim result As String
Dim nextFileNum As Long
Dim tempFile As String
Dim doc As Object
Dim objRoot As Object
Dim objChild As Object
Dim subChildren As Object
Dim subChild As Object
Dim subsubChildren As Object
Dim subsubChild As Object
tempFile = Environ("temp") & Application.PathSeparator & BookTitle & "-" & _
chapter & "-" & verse & XML_FILE_EXTENSION
' check if xml file already exists
If Len(Dir(tempFile)) = 0 Then
Set xml = CreateObject("MSXML2.XMLHTTP")
xml.Open "GET", _
"http://www.webservicex.net/BibleWebservice.asmx/GetBibleWordsByChapterAndVerse?BookTitle=" & _
BookTitle & "&chapter=" & chapter & "&Verse=" & verse, False
xml.Send
result = xml.responsetext
' parse result for response
result = Replace(Replace(result, "<", "<"), ">", ">")
' write to temp xml file
nextFileNum = FreeFile
Open tempFile For Output As #nextFileNum
Print #nextFileNum, result
Close #nextFileNum
End If
' create new XML doc
Set doc = CreateObject("MSXML2.DOMDocument")
doc.validateOnParse = False
doc.Load tempFile
Set objRoot = doc.documentElement
' loop through top-level children
For Each objChild In objRoot.childnodes
Set subChildren = objChild.childnodes
For Each subChild In subChildren
Set subsubChildren = subChild.childnodes
For Each subsubChild In subsubChildren
If subsubChild.nodename = "BibleWords" Then
GetBibleVerse = subsubChild.Text
If cacheQueries = False Then
Kill tempFile
End If
Exit Function
End If
Next subsubChild
Next subChild
Next objChild
End Function
Getting all verses from a given chapter
Retrieving all the Bible verses from a given chapter was a challenge. I decided it was best to return all the information from each node in the XML file and put it into a list box. That way we would see the verse, chapter, book and name, and then use the existing functionality (the GetBibleVerse function and the text box) to display verses.
' http://www.webservicex.net/WCF/ServiceDetails.aspx?SID=43
Dim xml As Object
Dim result As String
Dim nextFileNum As Long
Dim tempfile As String
Dim doc As Object
Dim objRoot As Object
Dim objChild As Object
Dim objTables As Object
Dim objTable As Object
Dim tempVerses() As String
Dim i As Long
Dim j As Long
tempfile = Environ("temp") & Application.PathSeparator & BookTitle & "-" & _
chapter & XML_FILE_EXTENSION
' check if xml file already exists
If Len(Dir(tempfile)) = 0 Then
Set xml = CreateObject("MSXML2.XMLHTTP")
xml.Open "GET", "http://www.webservicex.net/BibleWebservice.asmx/GetBibleWordsByBookTitleAndChapter?BookTitle=" & _
BookTitle & "&chapter=" & chapter, False
xml.Send
result = xml.responsetext
' parse result for response
result = Replace(Replace(result, "<", "<"), ">", ">")
' write to temp xml file
nextFileNum = FreeFile
Open tempfile For Output As #nextFileNum
Print #nextFileNum, result
Close #nextFileNum
End If
' create new XML doc
Set doc = CreateObject("MSXML2.DOMDocument")
doc.validateOnParse = False
doc.Load tempfile
Set objRoot = doc.documentElement
' loop through each Table level
Set objChild = objRoot.childnodes.Item(0)
Set objTables = objChild.childnodes
' size the array
ReDim tempVerses(1 To objTables.Length, 1 To objTables.Item(0).childnodes.Length)
For i = 0 To objTables.Length - 1
Set objTable = objTables.Item(i)
For j = 0 To objTable.childnodes.Length - 1
tempVerses(i + 1, j + 1) = objTable.childnodes.Item(j).Text
Next j
Next i
GetBibleVersesByChapter = tempVerses
End Function
