how to get bing api search results in MsOffice VBA

For the loyal VBA fans a quick snippet to grab search results from Bing in Access or Excel. There are few articles on the web about that, and that is a shame. So lets add some content on it to the web : first in MsAccess VBA and then in Excel, with a sample workbook added.

Working with the BING API requires an Application Id you can get at the Bing website.

Accessing the Bing Api in MsAccess VBA

In the the VBA Ide, through the menu Tools References I add a reference to the XML object library that contains the XMLHTTPRequest object, enabling me to make HTTP requests from VBA in the MsAccess database.

The simple part is the http request string :


        Dim MyKeyword As String
        MyKeyword = "seo"
       
        Dim requestString As String
        requestString = "http://api.bing.net/xml.aspx?"

        Dim AppId As String
        AppId = "(get one at the bing website)"

       ' Common request fields (required)
        requestString = requestString & _
        "AppId=" & AppId & "&Query=" & MyKeyword & "&Sources=Web"

        ' Common request fields (optional)
        requestString = requestString & _
              "&Version=2.0" _
            & "&Market=en-us" _
            & "&Adult=Moderate" _
            & "&Options="

        ' Web-specific request fields (optional)
        requestString = requestString & _
              "&Web.Count=10" _
            & "&Web.Offset=0" _
            & "&Web.Options=DisableHostCollapsing+DisableQueryAlterations"

Then comes the working part, sending the actual request and receiving the XML response (objSvrHTTP.responseText) containing the search data. I use a DomDocument object and XPath to get at the XML data.

First sending the request

    objSvrHTTP.Open "GET", requestString, False
    objSvrHTTP.send requestString

then creating a domdocument and receiving the response in it

    'Create the DomDocument Object
    Dim oDoc As MSXML2.DOMDocument
    Set oDoc = CreateObject("MSXML2.DOMDocument")
    oDoc.async = False
    oDoc.validateOnParse = False
     
    'Load the response in the DomDocument Object
    Dim fSuccess As Boolean
    fSuccess = oDoc.loadXML(objSvrHTTP.responseText)
    If Not fSuccess Then
        MsgBox "failed"
        Exit Sub
    End If

The response uses two namespaces and the data we actually want is in the “web” namespace, so before we start selecting nodes in the xml-tree we first indicate what namespace we want to access :

    oDoc.SetProperty "SelectionNamespaces", "xmlns:web='http://schemas.microsoft.com/LiveSearch/2008/04/XML/web'"

The total search result pages is a top level node in the namespace :

    Set xmlnode = oDoc.selectSingleNode("//web:Total")
    Debug.Print xmlnode.Text

…but the good stuff is in the “WebResult” nodes in the xml tree, I can use an XPath reference //web:Webresult to access that node-collection and with a simple for-next iterate through the collection, selecting the single nodes containing the actual data.

    
    Set oChildren = oDoc.selectNodes("//web:WebResult")
    For Each oResult In oChildren
        Debug.Print oResult.selectSingleNode("./web:Title").Text
        Debug.Print oResult.selectSingleNode("./web:Description").Text
        Debug.Print oResult.selectSingleNode("./web:Url").Text
        Debug.Print oResult.selectSingleNode("./web:DisplayUrl").Text
        Debug.Print vbCrLf
    Next

I output to the Immediate window aka Debug (under View, Immediate Window or Ctrl+G)

What can we do with it ? Let’s make a table for date, keyword, url, title, description, position

and add some code to store the results we retrieve

    Dim table1 As DAO.Recordset
    Set table1 = CurrentDb.OpenRecordset("Table1", dbOpenTable)
    'counter for position    
    i = 0
    
    Set oChildren = oDoc.selectNodes("//web:WebResult")
    For Each oStruct In oChildren

       i = i + 1
       With table1
            .AddNew
            'data from bing
            !TITLE = oStruct.selectSingleNode("./web:Title").Text
            !Description = oStruct.selectSingleNode("./web:Description").Text
            !url = oStruct.selectSingleNode("./web:Url").Text
            'and some additional data
            !MYDATE = Now()
            !POSITION = i
            !KEYWORD = MyKeyword
            'store the record
            .Update
        End With

    Next

    table1.Close

There now that scratches the seo itch :

MsAccess stuff is a bit hard to put on the web as sample, so I’ll make a quick one in Excel and put it up for download.

Accessing the Bing Api in Excel VBA

there you go : on sheet 1 you can fill in your AppID and keyword, click the butt(on…

…and on sheet 2 you get the top 10 search results

It uses the cells B1 and B2 as named ranges for MyKeyword and AppID, and has the code in the enclosed module. It also contains one of my AppId’s so no querying nasty shit, please.

integrating ms office and wordpress with vba and xml-rpc

Okay.

Yesterday I made some basic stuff to grab my WordPress blog data with visual basic for applicationsn cos I don’t feel like programming php admin pages for tables.

So let’s make a basic xml-rpc crud plugin and put my agenda on vba remote control.

The basic xml-rpc plugin is simple, plug extra methods into the xml-rpc method array, and write a function per crud-method.


add_filter( 'xmlrpc_methods', 'add_agenda_xmlrpc_methods' );

function add_agenda_xmlrpc_methods( $methods ) {
    $methods['agenda.addAgendaItem'] = 'addAgendaItem';
    $methods['agenda.updateAgendaItem'] = 'updateAgendaItem';
    $methods['agenda.deleteAgendaItem'] = 'deleteAgendaItem';
    $methods['agenda.reportAgendaItem'] = 'reportAgendaItem';
    return $methods;
}

function addAgendaItem($args) { }

function updateAgendaItem($args) { }

function deleteAgendaItem($args) { }

function reportAgendaItem($args) { }

//basic login helper function
function CheckLogin($user, $pwd) {}

There now, if I call on the xmlrpc.php file, the extra methods are added to the callback array and I can use the table CRUD functions from my vba desktop.

function addAgendaItem($args) {

        $blog_id	= (int) $args[0];
        $username	= $args[1];
        $password	= $args[2];
        $AgendaItem     = $args[3];

//remember : add a login check
//(for the example it is irrelevant)

        global $wpdb;
        $sql = "INSERT INTO ".$wpdb->prefix."Agenda (
                `userid`, `tags`, `description`, `firstdate`, `enddate`, `link`, `price`, `location`
                ) VALUES (
                '".$AgendaItem[0]['userid']."',
                '".$AgendaItem[0]['tags']."',
                '".$AgendaItem[0]['description']."',
                '".$AgendaItem[0]['firstdate']."',
                '".$AgendaItem[0]['enddate']."',
                '".$AgendaItem[0]['link']."',
                '".$AgendaItem[0]['price']."',
                '".$AgendaItem[0]['location']."'                
                )";
        $wpdb->query($wpdb->prepare($sql));

        return $wpdb->insert_id;
}

note : the agendaitem is a struct in an array (see below), I use [0] to get the first struct (which is the actual array with field-value pairs, my record with agenda info).

Activate the plugin, and write a simple test


'Type to hold an agenda info record
Type AgendaItem
    userid As String
    tags As String
    Description As String
    firstdate As String
    enddate As String
    link As String
    price As String
    location As String
End Type


Function AddAgendaItem()

txtURL = "http://wwwblog.com/xmlrpc.php"
txtUserName = "MyUsername"
txtPassword = "MyPassword"
  
  Dim objSvrHTTP As ServerXMLHTTP
  Dim strT As String
  Set objSvrHTTP = New ServerXMLHTTP
  
  objSvrHTTP.Open "POST", txtURL, False, CStr(txtUserName), _
   CStr(txtPassword)
  
  objSvrHTTP.setRequestHeader "Accept", "application/xml"
  objSvrHTTP.setRequestHeader "Content-Type", "application/xml"
  
    strT = ""
    strT = strT & ""
    strT = strT & "agenda.addAgendaItem"
    
    strT = strT & ""
    strT = strT & "" & txtBlogId & ""
    strT = strT & "" & txtUserName & ""
    strT = strT & "" & txtPassword & ""

'now we go make the struct, I use a Type (stdobject), normally
'you'd use a recordset

Dim a As AgendaItem

With a
    .userid = 1
    .Description = "rpc testing"
    .firstdate = "2009/10/14"
    .enddate = "2009/10/14"
    .link = "https://www.juust.org/"
    .price = "rpc testing"
    .location = "limmen"
    .tags = "php, xml-rpc"
End With

    strT = strT & ""
    strT = strT & ""
    
    strT = strT & ""

    strT = strT & "userid" & a.userid & ""
    strT = strT & "tags" & a.tags & ""
    strT = strT & "description" & a.Description & ""
    strT = strT & "firstdate" & a.firstdate & ""
    strT = strT & "enddate" & a.enddate & ""
    strT = strT & "link" & a.link & ""
    strT = strT & "price" & a.price & ""
    strT = strT & "location" & a.location & ""

'close the struct    
    strT = strT & ""
    strT = strT & ""

'close the struct array
    strT = strT & ""

'end parameters
    strT = strT & ""

'end method
    strT = strT & ""

'send the lot to the blog  
  objSvrHTTP.send strT

'print the response to debug 
  Debug.Print  objSvrHTTP.responseText

End function

Et voila :

agenda rpc

That’s yer basic Office-Wordpress XML-RPC integration.