Pulling RSS data in Excel (or: using Excel to search Craigslist)-part 2

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

Do you remember the last time we had built a spreadsheet that has tried to Craigslist via the RSS feed that is available for search results and pulled the information in Excel by using the XML mapping features little used.

Today, we will continue where we left off, adding the ability to search multiple sites and adding a little progress indicator if the search takes a long time.

Oh and by the end of this post, I've attached a version of the search tool that we invite you to use if you do not want to follow ... even if they do not give no promises as to the quality of the code-I'm sure there are optimizations that people can do, and I would love if you could send them back to me so I can learn from you!

Added the ability to search multiple sites in one fell swoop

Like every other step, this is not too complicated, it just takes some thought and time. Oh, and there I will be using VBA-Excel is great as is easily extended with VBA. To search multiple sites, we're going to create a table that lists all Craigslist sites that want to try, and then we will simply scroll this table whenever the user clicks the search button, grab the RSS results and adding them to our table XML that yesterday we setup. Then, let's start:

1. Switch to Sheet2 and adding a table (named Table2) with the following columns: "baseurl", "Research" and "fullurl"

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

= "/search/? query = "& rngSearchTerm &" & catAbb = sss & format = rss & areaID = 2 & subAreaID = "

3. in column BaseUrl, enter the base Url for some sites to Craigslist. I use these (make sure the fill down formulas from above in each of these lines:

This is all we have to do something for the table, now we just need to update our VBA RunSearch method to iterate through these different sites when you search using the FullUrl from our table above as the location to get RSS information. We will actually replace all VBA code that we created last time, by following these steps:

Click Developer | Visual Basic to open the VBA editor.If it is not already open, open Module1. Insert the following VBA subroutine to replace the search we created yesterday. Note that this is not much changed-lines that have changed since the last time that I highlighted:

Close the VBA editor, and with this, now you'll see that each time you click search, Excel chugs to seconds (more, depending on how many sites Search) and see all the search results displayed in the grid, added one after another. Regularity. Now if only Excel was more responsive, while research was happening ...

Adding a progress bar so that we know as the search will

We're almost there-we have our data coming through XML maps (with parameters with a search box) and displays the data for multiple sites. Just a couple more small VBA tweaks and there are a fairly functional research tool.

To add a progress indicator, we're just going to write "% done" in a worksheet cell. Sure, we could build a progress bar or something else, but this is Excel j we're talking about here. Since then we have a cycle who knows how many sites, we are searching, and the site in which we are currently in, should be as simple as dividing the two values and update the cell. Let's get started.

On Sheet1 (which shows the results of the search), select the cell to G3Format as a percentageClick formulas | Define the name and call the range "rngStatus" without quotes.Click Developer | Visual Basic and find upgrading subroutine attempts to have the following code. Note that, not much of this has changed-I highlighted lines that have changed since our last step:

And you're done! When you search, you'll see the progress bar update (and your search results update) checks for each site. Pretty cool if you ask me.

Of course, there are a lot of tinkering, you can make that make it even more interesting solution or clean up the code. I did a couple of them in the workbook that is linked to this post (added a stop button, did some additional factoring code, did a bit of formatting), but I'm sure others will have even more pleasant that tweaks can do. If some do, drop me a note with the changes-why use this workbook in real life! J

Cheers!

View the original article here