Pull Bible verses into Microsoft Excel

I would like to automate Bible verse lookup in Microsoft Excel.

The user would input the Bible verse reference into cell A1 and then a VBA function would put the ESV Bible verse cell B1.

My first thought was to pull the verse from a Bible website, using a technique shown here:

http://www.youtube.com/watch?v=7sZRcaaAVbg

However, I thought there might be an easier way than parsing the downloaded .html. The .html from biblegateway.com wasn't obvious to me.

Any ideas or suggestions?

Thanks,

Torrey

Find more posts tagged with

Comments

David Mitchell

The Biblia API will allow you to retrieve individual verses in a variety of formats, and it's likely that at least one of them will be suitable for your needs. More details (and examples) are available in the documentation at api.biblia.com.

Torrey Lewis

https://community.logos.com/discussion/comment/684621#Comment_684621

Thanks David.

I found an example here that is almost perfect:
http://api.biblia.com/v1/bible/content/LEB.html?passage=John3&style=fullyFormatted&key=fd37d8f28e95d3be8cb4fbc37e15e18e

However, the example is for the LEB Bible, whereas I need ESV. The end application is for Bible memorization, thus version is important. Other resources for the Bible memorization use ESV, so I need this application to match.

The issue I see is that ESV is not listed in the available Bibles:
http://api.biblia.com/v1/bible/find.xml?key=fd37d8f28e95d3be8cb4fbc37e15e18e

Any suggestions? I am hoping I am reading something wrong...

David Mitchell

https://community.logos.com/discussion/comment/684627#Comment_684627

You can see our list of available Bibles for the Biblia API here: http://api.biblia.com/docs/Available_Bibles

Typically, if we do not have a Bible, it is because our publisher prohibits the distribution of their content in this format. (in the particular case of the ESV, they have their own API)

Depending on how comfortable you are with text manipulation, you might also consider creating a Passage List in our desktop app and exporting it to a text file.

jack dooley

https://community.logos.com/discussion/comment/684627#Comment_684627

Torrey

Did you ever figure this out? I am trying to do the exact same thing and have been struggling with how to do it. If you made any headway, I would love to hear from you, it would be a huge huge help to me.

Jack

Jesse perl

https://community.logos.com/discussion/comment/1144333#Comment_1144333

Since the api seems to be having issues at the moment, this is the macro I made. It just inputs the text into the same cell. If you wanna change the translation, you'll just need to change where the URL is pointing to. (I'm assuming you don't need this anymore, but maybe someone in the future can use it)

Sub bibleVerseGrabber()
'
' bibleVerseGrabber Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
'
Dim spot As Range
Set spot = Selection
Dim IE As New InternetExplorer
IE.Visible = False
Dim BMV As String
BMV = Application.WorksheetFunction.EncodeURL(Selection.Value)
IE.navigate "https://biblia.com/bible/hcsb/" & BMV
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim bibVer As String
bibVer = Doc.getElementsByClassName("resourcetext")(0).innerText
IE.Quit
Dim superScript As Integer
Dim alphbet As String
superScript = 122

For superScript = 98 To superScript
alphbet = Chr(32) + Chr(superScript) + Chr(32)
bibVer = Replace(bibVer, alphbet, " ")
Debug.Print superScript
Next superScript

Selection = bibVer + Chr(40) + Selection.Value + Chr(41)
End Sub

Györgyi Láng

Dear Torrey,

I have read, You wanted to automate Bible verse lookup in Microsoft Excel. Could You arrenge it? We are in progress to make an app for Bible verse learning, and we have to put a lot of verses in Excel. Could You help us how can we make it faster and easier?

God bless!
Györgyi

lang.gy74@gmail.com