How to get RSS feed of google cse API in excel 2013 with VBA

[satish78]

Hi Friends,

Trying to get RSS feed of Google CSE API example https://www.googleapis.com/customsearch/v1?alt=atom&cx=[id]&key=[API key]&q= [keywords] into excel 2013 with VBA code.

I am trying to extract only first item of results (first item = title, url, description, date)

please help me with VBA code

Sheet2 I have list of google cse api urls like https://www.googleapis.com/customsearch/v1?alt=atom&cx=[id]&key=[API key]&q= [keywords]

In Sheet1 need imported data with column seperated.

 

[John_w]

Try this, though the results returned for the web site I searched doesn't have 'description' or 'date' XML elements so I have used 'summary' and 'updated' instead, the latter being an ISO8601 date-time string which is converted to an Excel date-time. You haven't said which column the URLs are in, so I have assumed column A of Sheet2.

You must set a reference to Microsoft XML v6.0, via Tools -> References in the VBA editor.

'Reference for early binding: Microsoft XML v6.0

Public Sub Custom_Search_All()
    
    Dim URLsSheet As Worksheet, resultsSheet As Worksheet
    Dim lastRow As Long, r As Long
    Dim result As Variant

    Set URLsSheet = ThisWorkbook.Worksheets("Sheet2")
    Set resultsSheet = ThisWorkbook.Worksheets("Sheet1")
    resultsSheet.Cells.ClearContents
    resultsSheet.Range("A1:D1").Value = Array("Title", "Link", "Summary", "Updated")
    
    With URLsSheet
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        For r = 2 To lastRow
            result = Google_CSE1(.Cells(r, "A").Value)
            resultsSheet.Cells(r, "A").Resize(1, UBound(result)).Value = result
        Next
    End With
    
End Sub

Public Function Google_CSE1(queryURL As String) As Variant
    
    Static XMLdoc As DOMDocument60
    Dim entry1 As IXMLDOMNode
    Dim results(1 To 4) As String
    
    'https://developers.google.com/custom-search/json-api/v1/reference/cse/list
    '
    'The cse.list method returns metadata about the search performed, metadata about the custom search engine used for the search, and the search results.
    '
    'This method requires three query parameters:
    '
    '   The search engine to use in your request (using the cx query parameter)
    '   The search terms for in this request (using the q query parameter).
    '   Your API key (using the key query parameter).
                    
    If XMLdoc Is Nothing Then Set XMLdoc = New DOMDocument60
    With XMLdoc
       
        'How To Specify Namespace when Querying the DOM with XPath - https://support.microsoft.com/en-us/help/294797
        
        'Search response starts with the following XML:
        '< ?xml version="1.0" encoding="UTF-8"? >
        '< feed gd:kind="customsearch#search" xmlns="http://www.w3.org/2005/Atom" xmlns:cse="http://schemas.google.com/cseapi/2010"
        'xmlns:gd="http://schemas.google.com/g/2005" xmlns:opensearch="http://a9.com/-/spec/opensearch/1.1/" >
        
        XMLdoc.async = False
        XMLdoc.validateOnParse = False
        XMLdoc.SetProperty "SelectionLanguage", "XPath"
        XMLdoc.SetProperty "SelectionNamespaces", "xmlns:a='http://www.w3.org/2005/Atom'"
        XMLdoc.Load queryURL
    End With
    
    Set entry1 = XMLdoc.SelectSingleNode("/a:feed/a:entry")
    results(1) = entry1.SelectSingleNode("a:title").Text
    results(2) = entry1.SelectSingleNode("a:link").Attributes.getNamedItem("href").Text
    results(3) = Replace(entry1.SelectSingleNode("a:summary").Text, vbLf, " ")   'remove multiple line chars
    results(4) = Cvt_ISO8601DT_Excel(entry1.SelectSingleNode("a:updated").Text)
    
    Google_CSE1 = results
    
End Function


Private Function Cvt_ISO8601DT_Excel(dt As String) As Date

    'Convert ISO8601 date time UTC (in the format yyyy-mm-ddthh-mm-ssz) to an Excel date-time
    '                                             1234567890123456789
    'https://en.wikipedia.org/wiki/ISO_8601#UTC
    
    Cvt_ISO8601DT_Excel = DateSerial(Mid(dt, 1, 4), Mid(dt, 6, 2), Mid(dt, 9, 2)) + TimeSerial(Mid(dt, 12, 2), Mid(dt, 15, 2), Mid(dt, 18, 2))
    
End Function

 

[satish78]

Working well except the extracted data converted with html code I think.

Please see the file attached.

https://spaces.hightail.com/space/2khQHDlKv6

 

[satish78]

Some times getting runtime error 91
Object variable or with block variable not set

 

[satish78]

Can you help me to extract all results as well?.

 

[John_w]

Some times getting runtime error 91
Object variable or with block variable not set

I think that happens when the CSE returns 0 results, which my code doesn't handle. One reason for 0 results is that the code is telling CSE to search for the wrong text (because it contains foreign characters) and therefore the 'q' parameter value must be URL-encoded.

To fix this, add the top function at https://stackoverflow.com/a/218199 and change my code to:

Public Function Google_CSE1(ByVal queryURL As String, searchText As String) As Variant
    queryURL = queryURL & "&q=" & URLEncode(searchText)

and

           result = Google_CSE1(.Cells(r, "A").Value, .Cells(r, "B").Value)

and delete the &q=searchText part of your Sheet1 URLs and instead put the search text in column B and pass it to the Google_CSE1 function as shown above.

 

[satish78]

I understand. But I am newbie to excel VBA. Do not know where to place the codes.
Could you help in providing clean VBA code which extracts all results. I appreciate your help.