1

I have a large list of words in column A. I would like to use excel to lookup the google or bing definition for each word in bulk. Excel has a built in function under research that will show you the bing definition, but you have to do it for each word manually.

I tried the method listed at the link below, but it is old and the function keeps returning the error "A value used in the formula is of the wrong data type" Finding the English definition of a word in VBA

If someone knows a program or website that will lookup the google definitions for a large list of words that would be helpful as well.

Community
  • 1
  • 1
user2799363
  • 11
  • 1
  • 2
  • In Excel 2013 there are many interactive worksheet formulae. You may call a number of APIs with the `WEBSERVICE()` one. I would highly recommend Oxford Dictionaries API, Google Dict API is **deprecated** [see link](http://stackoverflow.com/questions/22740789/how-to-use-to-google-dictionary-as-an-api). – user3819867 Jul 20 '15 at 11:35

1 Answers1

1

There should be more efficient ways to do this than my below code. It utilizes service from Dictionary.com.

You can use it as a function in worksheet, say you have "pizza" in A1, then in A2, you use =DictReference(A1) to show the definition. However I only coded it to return the first definition.

Option Explicit

Const URL_SEARCH = "http://dictionary.reference.com/browse/<WORD>?s=t"

Function DictReference(ByVal SearchWord As Variant) As String
    On Error Resume Next
    Dim sWord As String, sTxt As String
    sWord = CStr(SearchWord)
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", Replace(URL_SEARCH, "<WORD>", sWord), False
        .Send
        If .Status = 200 Then
            sTxt = StrConv(.ResponseBody, vbUnicode)
            ' The definition of the searched word is in div class "def-content"
            sTxt = Split(sTxt, "<div class=""def-content"">")(1)
            sTxt = Split(sTxt, "</div>")(0)
            ' Remove all unneccessary whitespaces
            sTxt = Replace(sTxt, vbLf, "")
            sTxt = Replace(sTxt, vbCr, "")
            sTxt = Replace(sTxt, vbCrLf, "")
            sTxt = Trim(sTxt)
            ' Remove any HTML codes within
            sTxt = StripHTML(sTxt)
        Else
            sTxt = "WinHttpRequest Error. Status: " & .Status
        End If
    End With
    If Err.Number <> 0 Then sTxt = "Err " & Err.Number & ":" & Err.Description
    DictReference = sTxt
End Function

Private Function StripHTML(ByVal sHTML As String) As String
    Dim sTmp As String, a As Long, b As Long
    sTmp = sHTML
    Do Until InStr(1, sTmp, "<", vbTextCompare) = 0
        a = InStr(1, sTmp, "<", vbTextCompare) - 1
        b = InStr(a, sTmp, ">", vbTextCompare) + 1
        sTmp = Left(sTmp, a) & Mid(sTmp, b)
    Loop
    StripHTML = sTmp
End Function
PatricK
  • 6,375
  • 1
  • 21
  • 25