You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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:
(leave empty for now)
="/search/?query=" & rngSearchTerm & "&catAbb=sss&format=rss&areaID=2&subAreaID="
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:
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:
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.
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
Again, this is great! Thanks!
Are you taking any suggestions for future articles or a part 3? I was thinking about taking this workbook you helped me make and making some more macros to 1)run the search every couple of hours of the 'Free' page of CL and 2) send an email (which goes right to my phone) once it runs this search. That way, any time of the day I'll have the heads up on any free items people are giving away.
Some problems I've thought of with this though is that I would need to make the table arrange itself with the newest posts at the top (I see your sheet does this but I haven't figured out how yet) and I would only want it to send an email if a NEW post has been made...(I'm thinking I could just do an if/then kind of thing to make a second table which the email pulls from)
Besides me not being proficient with VBA I think this is possible to do? Anyway this is just me thinking a little bit.
Again, thanks for this 2-part write up. It was great!