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?
LVL 53
Martin LissOlder than dirtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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 ;-).
0
Martin LissOlder than dirtAuthor Commented:
Sorry, no, I don't remember seeing that article before, but could you write code for me that does what I need?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Martin LissOlder than dirtAuthor 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?
0
Martin LissOlder than dirtAuthor Commented:
Also I don't want to have to install anything on my computer and please remember that I use XP
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtAuthor Commented:
The VB6 part is working but Member Ranking gives a 'Type Mismatch' error here
2018-02-18_10-35-35.png
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I forgot to mention that you need to be logged in permanently into EE using IE, because Web Query uses that.
0
Martin LissOlder than dirtAuthor Commented:
Please define "permanently". BTW I use Safari and not IE.
0
Martin LissOlder than dirtAuthor 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!
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
Martin LissOlder than dirtAuthor Commented:
I am permanently logged in but in Safari, not IE.
0
Martin LissOlder than dirtAuthor Commented:
Oh, and the Visual Basic data does work.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
Martin LissOlder than dirtAuthor 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.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Your screenshot shows the same numbers as mine regarding VB.
0
Martin LissOlder than dirtAuthor 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.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
For test, remove the numbers, e.g. the points. Then run again. If I'm correct they won't get filled in again.
0
Martin LissOlder than dirtAuthor Commented:
You ate correct.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I know :D.
So just start IE, log in once, and it should work.
0
Martin LissOlder than dirtAuthor 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.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
Martin LissOlder than dirtAuthor 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.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Experts Exchange

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.