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.

interesting : seo panel

That seems fun, an open source seo toolkit. It is a five second install multi-user package offering simple stats, but more interesting, a semi automated website directory submitter in a clean interface, and could be a valuable service offer if you run an seo community site.

It is PHP MVC and, what sparked my interest, it has a plugin interface.
I love that, It could be going somewhere over the next few years.

zend php and google webmaster tools api

update 2: Sandrine worked out a set of routines, as far as I know using Zend 1.7, she lists the code here.

update: Google updated their API in oktober (almost at the time I wrote these posts) and this code fails as it still based on the V1 APi. You can access the whole WT: toolset namespace (including sitemaps, verification) through the V2 API now, but you need to send a version id along with your request, that is handled in the new Zend 1.7 download.

The Problem

I can add 32.000 blogs on a standard WordPressMu install. How do I add 32.000 subdomains, verify them and add their sitemaps to Google Webmaster, without having to go to the webmaster page about 96.000 times ?

The solution

Integrating Google Webmaster Tools API into my WordPress Mu install.

What is it worth ?

If registering and verifying a site and adding a sitemap takes 5 minutes per domain, at E12,- per hour, that makes it 96.000 euros and 4 labor years for 32.000 sites. Writing a script is worth E96.000,- and saves me four years of mindless drone work, so that is well worth having a look at.

Software : Zend

Zend gData is a php framework that is programmed to handle Google Data. Their ClientLogin routine isn’t very flexible and they haven’t covered GWT Api yet, so I’ll have to hack some routines together.

After getting stonewalled by the zend program a few times, I went searching and ended up on ngoprekweb who have a nice post on ClientLogin authorization for the blogger api. Eris Ristemena uses a modified Zend ClientLogin, very nice work. I installed the adapted classes and tried that one to get through the ClientLogin, and it paid off.

The good stuff : Gwt api access

I am not interested in the blogger stuff though, I want access to GWT Google Webmaster Tools, so I worked Eris Ristemena’s blogger routine around a little.

set_include_path(dirname(__FILE__) . '/Zend_Gdata');
  require_once 'Zend.php';
  Zend::loadClass('Zend_Gdata_ClientLogin');
  Zend::loadClass('Zend_Gdata');
  Zend::loadClass('Zend_Feed');

  $username     = '';
  $password     = '';
  $service      = 'sitemaps';
  $source       = 'Zend_ZendFramework-0.1.1'; // companyName-applicationName-versionID
  $logintoken   = $_POST['captchatoken'];
  $logincaptcha = $_POST['captchaanswer'];

  try {
    $resp = Zend_Gdata_ClientLogin::getClientLoginAuth($username,$password,$service,$source,$logintoken,$logincaptcha);

    if ( $resp['response']=='authorized' )
    {
      $client = Zend_Gdata_ClientLogin::getHttpClient($resp['auth']);
      $gdata = new Zend_Gdata($client);

	  $feed = $gdata->getFeed("https://www.google.com/webmasters/tools/feeds/sites/");
         foreach ($feed as $item) {
	      echo '

'; } } elseif ( $resp['response']=='captcha' ) { echo 'Google requires you to solve this CAPTCHA image'; echo '

';
      echo '
‘; echo ‘Answer : ‘; echo ‘ ‘; echo ‘ ‘; echo ‘
';
      exit;
    }
    else
    {
      // there is no way you can go here, some exceptions must have been thrown
    }

  } catch ( Exception $e )  {
    echo $e->getMessage();
  }

(I added https://www.google.com/accounts/ to the captcha image source, otherwise it keeps drawing blanks.)

Zend uses a “HttpClient” for the connection to Google, and a gData class (usually the main ‘feed’, blogs, sites) that you use to do basic data manipulation. All feed entries are an atom format with a custom namespace.

Now I am going to add a domain. In my add_site function I put an XML Atom together to post (using the post() function of the gData class) to the sites feed url, and the Google API does the rest :

function add_site($domain, $client) {
		$xml='';
		$xml.='';
		$xml.='';
		$fdata = new Zend_Gdata($client);
		$result=$fdata->post($xml,"https://www.google.com/webmasters/tools/feeds/sites/");
		return $result;
}

In the main routine I pass the domain and the running httpclient to the add_site() function :

   if ( $resp['response']=='authorized' )
    {
      $client = Zend_Gdata_ClientLogin::getHttpClient($resp['auth']);
      echo add_site('test.blacknorati.com', $client);
    }

Cool. That saves me up to 32.000 site registrations. The rest of it is still greek to me, but this part functions. Next week : more nonsense (verify the site, add a sitemap, and integrate it in the blog creation function of wordpress mu).

1) about the blogger function : I tried to list the blogger posts with the ngoprekweb php code, but it seems blogger use a different string these days to identify the blog in gData, the id is returned as “tag:blogger.com-blabla-(blogid)” and you want the last part to access the blogs post atom feed :

	$idText = split('-', $item->id());
        $blogid = $idText[2];

(modified from the Zend 1.6.1 codebase)

      foreach ($feed as $item) {
        echo '' . $item->title() . '';

	$idText = split('-', $item->id());
        $blogid = $idText[2];

        $feed1 = $gdata->getFeed("http://www.blogger.com/feeds/$blogid/posts/summary");
//...
}