We help IT Professionals succeed at work.

Get Experts Exchange Profile Data

I keep statistics about my Experts Exchange points in an Excel 2010 workbook and every several days I update points that come from my profile. Here is what I do.
1) Go to my profile
2) Click 'Certifications' and then 'Member and Topic Rank'
3) Retrieve my Rank and Points
4) Click 'Topic Rank' and retrieve my Rank and Points for both Microsoft Excel and Visual Basic Classic
5) Click on the Visual Basic Topic and then under 'Top Experts For', click the left arrow once to get to 'Yearly' and retrieve the points for both the top expert and me (I could be both)

I use Windows XP as a virtual machine on my iMac, but you can assume it's just normal XP. Can someone write a macro for me that does that?
Comment
Watch Question

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Do you remember my code presented in one of my articles, https://www.experts-exchange.com/articles/3887/Getting-your-EE-Ranking-statistics-in-Excel-The-Next-Generation.html ?
I still use it, with yet unplublished changed code. There hasn't been enough of requests to lead to me posting the new code, but I can give out my workbook with some cleanup done.

However, it does not retrieve the top expert's data. And it would not fit well into the current code ;-).
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
Sorry, no, I don't remember seeing that article before, but could you write code for me that does what I need?
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I should have the necesary /admin/XML calls available (which are much better than having to use web automation), and I'll give it a try soon.
Can you give a short example of how it looks like, or post a small version of your current workbook?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
If you were to supply with a workbook that just put the values in column "A", that would be enough for me.

BTW, what are  /admin/XML calls?
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
Also I don't want to have to install anything on my computer and please remember that I use XP
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
I've managed to simplify and expand my mentioned workbook in a way you might be able to make use of it. It is running the internal JSP, hidden in the site code, called by using (Web) Query Tables.

The comparitive stuff for Visual Basic is in the first worksheet, Setup, starting at G11:
MartinLiss-stats.pnggenerated by calling getMemberRanking (button Member Ranking).
Note we need to know the TAID (topic area ID) for Visual Basic Classic, which is 93. The TAIDs can be extracted by debugging the web browser while calling features, or using http://www.experts-exchange.com/admin/xml/listTAs.jsp - which does only work for permissioned users (Topic Advisors aso.).
EE-Stats-MartinLiss.xlsm
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
The VB6 part is working but Member Ranking gives a 'Type Mismatch' error here
2018-02-18_10-35-35.png
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I forgot to mention that you need to be logged in permanently into EE using IE, because Web Query uses that.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
Please define "permanently". BTW I use Safari and not IE.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
The error is being generated because [ScratchArea].Range("B12") is blank, and strangely (to me)  qt.ResultRange's Row value is 26 and its Value2 is "You must be logged in to use this tool." from line 26 in this workbook!
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
As said, you need to log in using IE, and have "keep me logged in" checked (it is automatically). Web Query does only work with IE.
The "You must be logged in" is because IE has no login data, but does not allow for getting those statistics from logged-out view.

Safari does not work. You cannot automate that in any way, nor read web content.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
I am permanently logged in but in Safari, not IE.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
Oh, and the Visual Basic data does work.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Your "Oh, and the Visual Basic data does work." puzzled me, so I've debugged with being logged out in IE, and no, it does not work - neither the member ranking nor the VB stuff. It might look like the latter works, but it doesn't; the workbook I attached already contained the data as seen in the above screenshot, and the debugger is stopping at the very first ranking entry.

I probably should have moved the .Range("F1") = Mid(qt.ResultRange.Value2(10, 1), ... into the IF, then you would not see anything but empty cells ;-).

So back to what I told already - you need to be logged in using IE.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
I logged into EE on Safari and clicked the 'Points Current Month' button and got this
2018-02-19_11-01-16.pngwhere the marked values exactly match what I see now in my profile. So that seems to be working but the 'Member Ranking 'button throws the error mentioned above.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Your screenshot shows the same numbers as mine regarding VB.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
Ok, but they are today's results and they are different from the ones in your post ID 42473262. So as I said that seems to be working but the 'Member Ranking 'button throws an error.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
For test, remove the numbers, e.g. the points. Then run again. If I'm correct they won't get filled in again.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
You ate correct.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I know :D.
So just start IE, log in once, and it should work.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
  1. Logged out of EE
  2. Logged back In
  3. Went to Profile page
  4. Opened your workbook
  5. Cleared all the numbers
  6. Clicked Points Current Month

All numbers still blank.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
You logged in using Internet Explorer?

If nothing helps, you can debug by setting a breakpoint at a qt.Refresh, run the code, then use the context menu in the ScratchArea range (starting at A26) on worksheet Setup to edit the query. This should open a web browser window. You should see "You must be logged in to use this tool". Change the URL to just www.experts-exchange.com, log in, and after getting the logged in view of EE, cancel the query to restore the original URL. Now you can continue to run VBA.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
I hadn't been logging in with IE because I run Win XP as a virtual machine on my iMac and the version of IE that I have in Windows is way down-level, and in any case Safari is much more convenient for me to use. But when I did log in with IE your code worked! Now I just need to modify it so that it automatically directs me to IE.

Thank you for your time and effort.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
I agree it is pretty unsafe to use the IE in XP, but for this purpose it should not be an issue. And you only should need to log in once into IE.