{"id":743,"date":"2010-05-01T12:33:28","date_gmt":"2010-05-01T12:33:28","guid":{"rendered":"http:\/\/andyparkes.co.uk\/blog\/?p=743"},"modified":"2010-05-01T13:44:59","modified_gmt":"2010-05-01T13:44:59","slug":"sharepoint-as-a-twitter-client-sort-of-part-two","status":"publish","type":"post","link":"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/","title":{"rendered":"SharePoint as a Twitter Client. Sort of – Part Two"},"content":{"rendered":"

I intended to follow on from part one<\/a> a bit sooner but it\u2019s been really busy at the office!<\/p>\n

In part one I gave a brief overview of what I was trying to accomplish and how to get a rough prototype working.<\/p>\n

While it did the job I highlighted the following limitations.<\/p>\n

    \n
  1. It only fetches the last twenty updates \u2013 if there have been twenty five updates since you last did it then you\u2019re out of luck. <\/li>\n
  2. Conversely, if there have only been a few updates it\u2019ll still fetch the last twenty giving you duplicate items. <\/li>\n
  3. There is also the problem of needing to run this manually.  <\/li>\n
  4. It doesn\u2019t automatically update the SharePoint list. We have to repeat the list creation process every time.<\/li>\n
  5. Finally, it doesn\u2019t deal with protected Twitter accounts at all! <\/li>\n<\/ol>\n

     <\/p>\n

    This post will deal with the items 1, 2 and 5 and is actually pretty easy.<\/p>\n

    We\u2019re going to use a bit of VBA to make Access a little smarter. I\u2019m not a programmer\/developer so if the code looks silly or inefficient\u2026.there is a good reason for it!<\/p>\n

    Also you\u2019ll notice a distinct lack of error handling. This is just an exercise to see if it would work so code is kept to a minimum! <\/p>\n

    The Twitter API method I used in the previous post had a parameter where you can specify a tweet ID and the API will return all tweets since then.<\/p>\n

    http:\/\/api.twitter.com\/1\/statuses\/user_timeline.xml?screen_name=AndyParkes&since_id=13081868261<\/a><\/p>\n

    So the only difficulty now is actually figuring out what tweet ID to use.<\/p>\n

    If only we had some sort of list we could query to do this. \ud83d\ude42<\/p>\n

    Again this is really easy as we\u2019re using Access which is built for the job!<\/p>\n

    Here\u2019s the bit of SQL I crafted<\/p>\n

    "SELECT TOP 1 VAL(status.id) as TwitID FROM status ORDER BY VAL(status.id) DESC;"<\/p>\n

    I\u2019m using the VAL function to convert the tweet ID to a number as I had some weird sorting issues.<\/p>\n

    I wrapped this is in a VBA function called \u201cGet_Last_Tweet_ID\u201d<\/p>\n

    \n

    Function Get_Last_Tweet_ID() As String
    Dim db As Database
    Dim strSql As String
    Dim rstData As DAO.Recordset
    Dim strID As String <\/p>\n

    Set db = CurrentDb()
    strSql = "SELECT TOP 1 VAL(status.id) as TwitID FROM status ORDER BY VAL(status.id) DESC;" <\/p>\n

    Set rstData = db.OpenRecordset(strSql) <\/p>\n

        If rstData.EOF = False Then
            strID = rstData("TwitID")
        Else
            strID = ""
        End If <\/p>\n

        rstData.Close
        Set rstData = Nothing <\/p>\n

    Get_Last_Tweet_ID = strID <\/p>\n

    End Function<\/p>\n<\/blockquote>\n

     <\/p>\n

    So this function will simply return a tweet ID we can plug into the API call and it\u2019ll only return the most recent tweets. If the function returns an empty string we just don\u2019t bother to run the API call.<\/p>\n

    The next question you\u2019re probably asking is HOW do you run the API call?<\/p>\n

    Remember the wizard we ran in the last post to import the XML? All that wizard does is use a VBA function called ImportXML<\/p>\n

    The syntax is simple<\/a><\/p>\n

    Application.ImportXML(DataSource<\/em>, ImportOptions<\/em>)<\/strong><\/p>\n

    The parameters are the same options asked for by the wizard.<\/p>\n

    A data source and whether you want to import the data, the structure or both.<\/p>\n

    That\u2019s the first part \u2013 but since I want to be able to take protected accounts into consideration we can\u2019t just & strScreenName &     "&since_id=" & strLastID, "False", strUser, strPassword
    End If <\/p>\n

    myXML.send <\/p>\n

    Get_Latest_Tweets = myXML.responseText <\/p>\n

    End Function<\/p>\n<\/blockquote>\n

    Note: Depending on what you have on your machine you may need to use \u201cMicrosoft.XMLHTTP\u201d instead.<\/em><\/p>\n

    This function takes a couple of parameters, the ScreenName of the Twitter account to retrieve, a username and password for authentication and a Tweet ID.<\/p>\n

    We check to see if we passed an ID. If not then don\u2019t use the \u201csince_id\u201d parameter and just fetch the last 20 tweets (the default). We could add an extra parameter to fetch a specific number of tweets.<\/p>\n

    We then send the response and the function returns a string containing some XML.<\/p>\n

    Now that we have our XML from Twitter what now?<\/p>\n

    Since the ImportXML wizard requires a file to work with we need to save the XML to disk.<\/p>\n

    \n

    Sub SaveTmpXML(strXML As String)
         ‘ Load the XML
         Dim xmlDoc
         Set xmlDoc = CreateObject("Microsoft.XMLDOM")
         xmlDoc.async = "false"
         xmlDoc.loadXML strXML
        xmlDoc.Save strTempFile
    End Sub<\/p>\n<\/blockquote>\n

     <\/p>\n

    This is a really simple procedure. We pass the XML to it, create an XMLDOM object, load the XML string and save it disk. I used a global variable for the filename so I can specify a temp path in an options table which I\u2019ll cover later.<\/p>\n

    Finally we can import the XML into our table,<\/p>\n

    \n

    Public Function Fetch_Tweets() As String <\/p>\n

    Dim strID As String
    Dim strXML As String <\/p>\n

    If Get_Settings = False Then
        Debug.Print "Couldn’t get settings"
        End
    End If<\/p>\n

    strID = Get_Last_Tweet_ID
    strXML = Get_Latest_Tweets(strScreenName, strUserName, strPassword, strID) <\/p>\n

    SaveTmpXML strXML <\/p>\n

    Application.ImportXML strTempFile, acAppendData<\/p>\n

    Tidy_User_table <\/p>\n

    End Function<\/p>\n<\/blockquote>\n

     <\/p>\n

    This a wrapper function that calls all the code I\u2019ve outlined above.<\/p>\n

    We find the last Tweet ID, fetch the tweets from Twitter, save them to an XML file and finally import them into our table.<\/p>\n

    You\u2019ll notice the Get_Settings<\/em> and Tidy_User_Table <\/em>procedure calls, I\u2019ll also cover those in a later post.<\/p>\n

    You could hook this wrapper function up to a button on a form. Once clicked it would update the table in the database with the latest tweets. <\/p>\n

    I think that\u2019s all three issues solved. We now only fetch exactly the tweets we need and can work easily with protected tweets (Twitter recently announced a change that will break this but I\u2019ll cover that in an upcoming post)<\/p>\n

    I\u2019ll get part three together a bit sooner!<\/p>\n","protected":false},"excerpt":{"rendered":"

    I intended to follow on from part one a bit sooner but it\u2019s been really busy at the office! In part one I gave a brief overview of what I was trying to accomplish and how to get a rough prototype working. While it did the job I highlighted the following limitations. It only fetches<\/p>\n

    <\/span>Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[106,45,87],"tags":[],"class_list":["post-743","post","type-post","status-publish","format-standard","hentry","category-access-office","category-sharepoint","category-twitter"],"yoast_head":"\nSharePoint as a Twitter Client. Sort of – Part Two - Andy's Techie Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SharePoint as a Twitter Client. Sort of – Part Two - Andy's Techie Blog\" \/>\n<meta property=\"og:description\" content=\"I intended to follow on from part one a bit sooner but it\u2019s been really busy at the office! In part one I gave a brief overview of what I was trying to accomplish and how to get a rough prototype working. While it did the job I highlighted the following limitations. It only fetchesRead More\" \/>\n<meta property=\"og:url\" content=\"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/\" \/>\n<meta property=\"og:site_name\" content=\"Andy's Techie Blog\" \/>\n<meta property=\"article:published_time\" content=\"2010-05-01T12:33:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2010-05-01T13:44:59+00:00\" \/>\n<meta name=\"author\" content=\"Andy Parkes\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Andy Parkes\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/\",\"url\":\"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/\",\"name\":\"SharePoint as a Twitter Client. Sort of – Part Two - Andy's Techie Blog\",\"isPartOf\":{\"@id\":\"https:\/\/andyparkes.co.uk\/blog\/#website\"},\"datePublished\":\"2010-05-01T12:33:28+00:00\",\"dateModified\":\"2010-05-01T13:44:59+00:00\",\"author\":{\"@id\":\"https:\/\/andyparkes.co.uk\/blog\/#\/schema\/person\/3534e8ac6b1bec765cd061feff56679d\"},\"breadcrumb\":{\"@id\":\"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/andyparkes.co.uk\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SharePoint as a Twitter Client. Sort of – Part Two\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/andyparkes.co.uk\/blog\/#website\",\"url\":\"https:\/\/andyparkes.co.uk\/blog\/\",\"name\":\"Andy's Techie Blog\",\"description\":\"Professional Geek\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/andyparkes.co.uk\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/andyparkes.co.uk\/blog\/#\/schema\/person\/3534e8ac6b1bec765cd061feff56679d\",\"name\":\"Andy Parkes\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/andyparkes.co.uk\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/3824cbf53df51d7ca5cf809b6ad81a157fbfff2292e36ab8666f04ddad06bfcc?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/3824cbf53df51d7ca5cf809b6ad81a157fbfff2292e36ab8666f04ddad06bfcc?s=96&d=mm&r=g\",\"caption\":\"Andy Parkes\"},\"description\":\"Andy Parkes is Technical Director at Coventry based IT support company IBIT Solutions. Formerly, coordinator of AMITPRO and Microsoft Partner Area Lead for 2012-2013. He also isn't a fan of describing himself in the third person.\",\"sameAs\":[\"http:\/\/www.andyparkes.co.uk\/blog\"],\"url\":\"https:\/\/andyparkes.co.uk\/blog\/index.php\/author\/andyparkes\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SharePoint as a Twitter Client. Sort of – Part Two - Andy's Techie Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/","og_locale":"en_US","og_type":"article","og_title":"SharePoint as a Twitter Client. Sort of – Part Two - Andy's Techie Blog","og_description":"I intended to follow on from part one a bit sooner but it\u2019s been really busy at the office! In part one I gave a brief overview of what I was trying to accomplish and how to get a rough prototype working. While it did the job I highlighted the following limitations. It only fetchesRead More","og_url":"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/","og_site_name":"Andy's Techie Blog","article_published_time":"2010-05-01T12:33:28+00:00","article_modified_time":"2010-05-01T13:44:59+00:00","author":"Andy Parkes","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Andy Parkes","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/","url":"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/","name":"SharePoint as a Twitter Client. Sort of – Part Two - Andy's Techie Blog","isPartOf":{"@id":"https:\/\/andyparkes.co.uk\/blog\/#website"},"datePublished":"2010-05-01T12:33:28+00:00","dateModified":"2010-05-01T13:44:59+00:00","author":{"@id":"https:\/\/andyparkes.co.uk\/blog\/#\/schema\/person\/3534e8ac6b1bec765cd061feff56679d"},"breadcrumb":{"@id":"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/andyparkes.co.uk\/blog\/index.php\/2010\/05\/01\/sharepoint-as-a-twitter-client-sort-of-part-two\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/andyparkes.co.uk\/blog\/"},{"@type":"ListItem","position":2,"name":"SharePoint as a Twitter Client. Sort of – Part Two"}]},{"@type":"WebSite","@id":"https:\/\/andyparkes.co.uk\/blog\/#website","url":"https:\/\/andyparkes.co.uk\/blog\/","name":"Andy's Techie Blog","description":"Professional Geek","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/andyparkes.co.uk\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/andyparkes.co.uk\/blog\/#\/schema\/person\/3534e8ac6b1bec765cd061feff56679d","name":"Andy Parkes","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/andyparkes.co.uk\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/3824cbf53df51d7ca5cf809b6ad81a157fbfff2292e36ab8666f04ddad06bfcc?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3824cbf53df51d7ca5cf809b6ad81a157fbfff2292e36ab8666f04ddad06bfcc?s=96&d=mm&r=g","caption":"Andy Parkes"},"description":"Andy Parkes is Technical Director at Coventry based IT support company IBIT Solutions. Formerly, coordinator of AMITPRO and Microsoft Partner Area Lead for 2012-2013. He also isn't a fan of describing himself in the third person.","sameAs":["http:\/\/www.andyparkes.co.uk\/blog"],"url":"https:\/\/andyparkes.co.uk\/blog\/index.php\/author\/andyparkes\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pmvJ6-bZ","jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/andyparkes.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/743","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andyparkes.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andyparkes.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andyparkes.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/andyparkes.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=743"}],"version-history":[{"count":1,"href":"https:\/\/andyparkes.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/743\/revisions"}],"predecessor-version":[{"id":744,"href":"https:\/\/andyparkes.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/743\/revisions\/744"}],"wp:attachment":[{"href":"https:\/\/andyparkes.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=743"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andyparkes.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=743"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andyparkes.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=743"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}