Chandoo Member List

[Luke M]

Partly out of my own curiousity to see all the data, and partly as a challenge to myself to learn how to extract web data into XL, I built a worksheet that (tries) to go to every member page here at Chandoo and extract basic info:
User Name
Date of Last Activity
Date Joined
Message Count
Likes
Trophy Points

Finally got it to do a "good-enough" job, and thought I would share. I ran into problems when member privacy settings required a login to view, hence the long block of blank rows at end of data (when we migrated to new forum w/ new default privacy settings). I also put together a basic graph showing # of people joining our forum per month.


I would love it if someone with more Web experience could improve on my code, or has another interesting analaysis (or a easy way to get this data).

 

[Debraj]

Splendid.. awesome..

PS: Is this some kind of hacking.. or what!!

 

[Luke M]

Splendid.. awesome..

PS: Is this some kind of hacking.. or what!!

Not really. It should be no different than typing in the web address yourself manually and reading the info. I just had to learn how to have XL open the web page, and read the HTML tags to get the parts that I was interested in. Runs terribly slow if you try to get all the members at once, but for my first XL-Internet project, I'm a little proud.

 

[SirJB7]

Hi, Luke M!

Congratulations and welcome to the world of publicly available information & data theft... ehmmm... acquisition, I mean.

A few comments after a slightly first glance:
- worksheet name should be Data instead of Sheet1
- very clever use of MSXML2.XMLHTTP instead of Object Browser (as no interface usage required)
- you used Option Explicit, I can't believe it... do you feel well, should I call the doctor or 911?
- web access is slow when not motionless
- I'd use arrays (don't know if with Redim Preserve or fixing upper bound to 15K) and at the end download them to table columns G:M... well, this is my first thought with any process alike but thinking it again in this case it'd probably be irrelevant regarding create object time... just give a try if you're curious
- I'd use early binding to speed up creation object process

I'll send you a mail with extra info about this kinda crawler that might help with finding tags and co.

Again, congratulations and welcome to this new world. And remember that law principle that says "in a state of freedom, which is not explicitly prohibited is implicitly permitted".

Regards!

 

[Luke M]

Hi, Luke M!
- you used Option Explicit, I can't believe it... do you feel well, should I call the doctor or 911?

Yep, starting to try and develop this habit. The folks over at OzGrid (who I recently joined) like to enforce that rule, so I decided it was time to learn.

 

[SirJB7]

Hi, Luke M!
So in less than a month you're following what being told "there" and in almost 3 years and half you didn't follow what been told "here" (by me, at least)? That's 35:1 ratio... a mere 2,86% of influence between this awesome site and "that" other one...
Regards!

 

[Luke M]

To quote Théoden,

You have no power here!

I don't do it because I think it's necessarily better, I just do it because I need my posts over there approved, so I can reach 250 posts, so I can start using the paid Excel projects, so I can make some extra $$$.

 

[SirJB7]

@Luke M
Hi!
Didn't know about it, gonna take a look later.
Regards!
PS: BTW, it's necessarily better!!!

 

[Ratan Bhushan]

Hi sir

I am trying to run the macro now, its not working as it is closing the excel.

Please advise