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

satish78

Board Regular
Joined
Aug 31, 2014
Messages
188

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,276
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.

Code:
'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

Board Regular
Joined
Aug 31, 2014
Messages
188
Some times getting runtime error 91
Object variable or with block variable not set
 

satish78

Board Regular
Joined
Aug 31, 2014
Messages
188
Can you help me to extract all results as well?.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,276
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:

Code:
Public Function Google_CSE1(ByVal queryURL As String, searchText As String) As Variant
    queryURL = queryURL & "&q=" & URLEncode(searchText)
and
Code:
           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

Board Regular
Joined
Aug 31, 2014
Messages
188
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,699
Messages
5,470,257
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top