Forum Discussion

Qrew Cadet
4 years ago

Get HTML from Web Page DOM using Data from QB fields

I have a database of patents.  Each record includes a patent number field, a claim number field, and a claim field.  I want to extract the corresponding text of the patent claim from Google Patents using the patent number and claim number.  I've written code in VBA that performs this function to populate a spreadsheet (which I import and merge by record ID).  I figure it would be better to directly pull the data from Google patents.

I've more or less figured out the DOM for Google patents.   Here is the relevant VBA code.  
--patno is the patent number.  It's alphanumeric string CCnnnnnnn where CC is country code string "US", "EP".  I use the country code extract to select how to pull the data from Google patents, since the DOMs for countries are different. 
--claimnumber is the number of the claim to be extracted. 
--claim is the text.

    url = "" & patno & "/en"
    ' Fetch page at URL
    With CreateObject("WINHTTP.WinHTTPRequest.5.1")
        .Open "GET", url, False
        patent.body.innerHTML = .responseText
    End With
    Set pElements = patent.getElementsByClassName("claim")
    Set pElement = pElements(claimNumber)
    claim = pElement.innerText

Once I have claim as text, I would save it to the relevant field.  For purposes of discussion assume my QB uses the same field names (patno, claimnnumber, claim).

What would the equivalent javascript and QB code be for something like this?

I've done lots of searching in the QB community and have found only a couple discussions of using js to pull data from other website, but nothing I felt I could generalize.




5 Replies

  • Robert, does the patent service have a API call which will respond in JSON format.  If so then a Pipeline is the low code no code way to go.

    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
  • PaulPeterson1's avatar
    Qrew Assistant Captain

    It looks like you using screen scrapping to get your data.  You will probably need an external source to collect your data, then you can import the data into your app.  Most sites (including Google) have policies regarding screen scraping.  If you make too many calls they will eventually block your IP.

    Paul Peterson
    • ROBERTSACHS's avatar
      Qrew Cadet
      Yes, I'm using screen scraping.  But first the data is public data and the volume is trivially low (a few calls per day).  Google used to have an API for this, but discontinued it.