RSS feed plus Twitter share data into an excel file?

I have what might be an advanced problem I need to solve. To you maybe it's not advanced, to me, it is : ).

By way of example, here's what I'm after. On popular blogs, you'll often see a blog article and on the left side of the article will be the Social share icons/chicklets (such as an icon to share the article on Twitter, one to share on Facebook etc). Those share icons normally also show the number of times this article has been shared on that social network.

I want to have an xls file with 5 columns that will load the RSS feed of a particular blog site, plus the social share count, and come out with the following columns:

Column 1: Name of the blog
Column 2: Title of the blog article
Column 3: Date of the blog article
Column 4: URL to the blog article
Column 5: Number of Twitter shares that that article has had

It's Column #5 that is the tough one.

I'm sure someone could easily whip up an xls with columns 1-4 for me, but does someone have an xls file that they can share with me that can pull in the number of Twitter shares as well?

Image of the Twitter shares data I'm talking about:
twittershares.jpg

Image of what the final xls file would look like:
xls.png

(Note: I have Excel 2003 now, but can upgrade to 2007 if necessary)

You are going to need to learn how to use references to Microsoft HTML Object Library and Microsoft Internet Controls or else how do you intend to be able to even tell VBA what elements you want to grab from the page? And different web sites contain different ID value and names, so you may need to create different macros for different sites, or make if-then statements. Even though this is VBA, this is more of an IE automation thing, and this isnt probably the greatest forum for asking about IE automation. In this case, google is your friend.

http://www.w3schools.com/htmldom/default.asp
http://www.access-programmers.co.uk/...d.php?t=195033
http://www.makeuseof.com/tag/basic-i...t-browser-vba/
http://www.excely.com/excel-vba/ie-automation.shtml


If you do not have a basic understanding of VBA, you are going to be in the doghouse with such a monumental project

There's no need to use Internet Explorer, rss feeds are just xml and libraries are available for parsing xml.

The twitter things is a little more complex, but not much, we just do a direct call to the twitter api passing in the url of the article, for the feedburner link from the blogspot page:

Public Sub GetData()

Const url As String = "http://feeds.feedburner.com/HubSpot?fmt=xml"

Dim oXML As Object
Dim oNodes As Object
Dim BlogTitle As String
Dim x As Long
Dim node As Object

With CreateObject("MSXML2.DOMDocument")
    .Load url
    Do: DoEvents: Loop Until .readystate = 4
    Set oNodes = .getelementsbytagname("item")
    BlogTitle = .getelementsbytagname("title").Item(0).Text
End With

ReDim Data(1 To oNodes.Length, 1 To 5)

For Each node In oNodes
    With node
        Data(x + 1, 1) = BlogTitle
        Data(x + 1, 2) = .ChildNodes(2).Text  'Title
        Data(x + 1, 3) = .ChildNodes(8).Text   'Link
        Data(x + 1, 4) = .ChildNodes(6).Text   'PubDate
        Data(x + 1, 5) = tweetCount(.ChildNodes(8).Text)
    End With
    x = x + 1
Next node

Sheet1.Cells(1, 1).Resize(UBound(Data), UBound(Data, 2)).Value = Data

End Sub

Public Function tweetCount(url As String) As Long

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://cdn.api.twitter.com/1/urls/count.json?url=" & url & "&callback=twttr.receiveCount"
        .send
        Do: DoEvents: Loop Until .readystate = 4
        On Error Resume Next
        tweetCount = Val(Split(.responsetext, "ount"":")(1))
    End With
    
End Function

Originally Posted by Kyle123

There's no need to use Internet Explorer, rss feeds are just xml and libraries are available for parsing xml.

The twitter things is a little more complex, but not much, we just do a direct call to the twitter api passing in the url of the article, for the feedburner link from the blogspot page:

Public Sub GetData()

Const url As String = "http://feeds.feedburner.com/HubSpot?fmt=xml"

Dim oXML As Object
Dim oNodes As Object
Dim BlogTitle As String
Dim x As Long
Dim node As Object

With CreateObject("MSXML2.DOMDocument")
    .Load url
    Do: DoEvents: Loop Until .readystate = 4
    Set oNodes = .getelementsbytagname("item")
    BlogTitle = .getelementsbytagname("title").Item(0).Text
End With

ReDim Data(1 To oNodes.Length, 1 To 5)

For Each node In oNodes
    With node
        Data(x + 1, 1) = BlogTitle
        Data(x + 1, 2) = .ChildNodes(2).Text  'Title
        Data(x + 1, 3) = .ChildNodes(8).Text   'Link
        Data(x + 1, 4) = .ChildNodes(6).Text   'PubDate
        Data(x + 1, 5) = tweetCount(.ChildNodes(8).Text)
    End With
    x = x + 1
Next node

Sheet1.Cells(1, 1).Resize(UBound(Data), UBound(Data, 2)).Value = Data

End Sub

Public Function tweetCount(url As String) As Long

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://cdn.api.twitter.com/1/urls/count.json?url=" & url & "&callback=twttr.receiveCount"
        .send
        Do: DoEvents: Loop Until .readystate = 4
        On Error Resume Next
        tweetCount = Val(Split(.responsetext, "ount"":")(1))
    End With
    
End Function

Wow, yeah makes sense to go for the RSS feed then, didnt even think about that. That is way too cool.

There's usually always a better option that automating IE, it's really slow for numerous reasons. You can normally call services directly without involving a browser, all you need are development tools for the browser - that's where I found the twitter api call. It is requested for each article on the blog roll.