Pulling RSS data into Excel (or: Using Excel to Search Craigslist) - Part 2

This blog post is brought to you by Dan Battagin a Lead Program Manager on the Excel team.

You'll remember last time that we'd built a spreadsheet that searched Craigslist via the RSS feed that's available for search results, and pulled the information into Excel using the little-used XML Map feature.

Today, we'll continue on where we left off, adding the ability to search multiple sites, and adding a little progress indicator in case the search takes a long time.

Oh, and at the end of this post, I've attached a version of the search tool that you're welcome to use if you don't want to follow along...though I give no promises as to the quality of the code - I'm sure there are optimizations that folks can make, and I'd enjoy if you could send those back to me so I can learn from you!

Adding the Ability to Search Multiple Sites in One Fell Swoop

Like every other step, this one isn't too complicated, it just takes a bit of thinking and time. Oh, and we'll be using VBA again - it's great how easily Excel is extended with VBA. To search multiple sites, we're going to create a table that lists all of the Craigslist sites we want to search, and then we'll simply iterate through that table whenever the user clicks the search button, grabbing the RSS results and appending them to our XML table that we setup yesterday. So, let's get started:

1. Change to Sheet2 and add a Table (called Table2) with the following columns: "BaseUrl", "Search", and "FullUrl"

2. Next, add these formulas in the first row of the table:

3. In the BaseUrl column, enter the base Url's for a few Craigslist sites. I'll use these (make sure that your formulas from above fill down into each of these rows:

• http://portland.craigslist.org
• http://sacramento.craigslist.org
• http://sandiego.craigslist.org
• http://seattle.craigslist.org
• http://sfbay.craigslist.org
• http://bellingham.craigslist.org
• http://slo.craigslist.org

That's all we have to do really for the table, now we just need to update our RunSearch VBA method to cycle through these different sites when running the search, using the FullUrl from our table above as the location to get RSS information. We'll actually replace all of the VBA code that we created last time using the following steps:

1. Click Developer | Visual Basic to open up the VBA editor.
2. Open Module1, if it's not already open.
3. Insert the following VBA to replace the Search subroutine that we created yesterday. Note that not much of this changed - I've highlighted the lines that have changed from last time:

Close the VBA editor, and with that, you'll now see that each time you click Search, Excel chugs for a few seconds (longer, depending on how many sites you search) and ultimately you see all the search results shown in the grid, appended one after the other. Neatness. Now, if only Excel was more responsive while the search was going on...

Adding the Status Indicator So We Know How Much Longer the Search Will Take

We're almost there - we've got our data coming in through XML Maps (parameterized with a Search Box) and showing data for multiple sites. Just a couple more little VBA tweaks, and we'll have a pretty functional search tool.

To add the progress indicator, we're just going to write the "% Done" into a cell in the worksheet. Sure, we could build a progress bar or something else fancy, but this is Excel we're talking about here J. Since we already have a loop that knows how many sites we're searching, and which site we're currently on, it should be as simple as dividing the two values and updating the cell. Let's get started.

1. On the Sheet1 (which shows your search results), select G3
2. Format the cell as a percentage
3. Click Formulas | Define Name, and call the range "rngStatus" without quotes.
4. Click Developer | Visual Basic and find the update your Search subroutine to have the following code. Note that not much of this changed - I've highlighted the lines that have changed from our last step:

And that's it! When you run your search, you'll see the Status indicator updating (and your search results updating) as each site is searched. Pretty cool if you ask me.

Of course, there are lots of tweaks you can make that will clean up the code or make the solution even cooler. I've made a few of them in the workbook that is attached to this post (added a stop button, did some additional factoring of the code, did a bit of formatting), but I'm sure others will have even nicer tweaks that they can make. If you do make some, drop me a note with the changes - because I actually use this workbook in real life! J

Cheers!