Examine Experts-Exchange data

Martin Liss
Martin Liss used Ask the Experts™
on
I'm trying to use code that I don't understand to gather statistics about myself from experts-exchange. The code begins with the following. Note the cryptic comment at line 12. In any case when I run the code and enter https://www.experts-exchange.com in the input box I get an "A security error occurred" error at line 26. What am I doing wrong?
Private Sub HTML_VBA_Excel()
    Dim oXMLHTTP As Object
    Dim sPageHTML  As String
    Dim sURL As String
    Dim intName As Integer
    Dim intEnd As Integer
    Dim strParts() As String
    Dim strStartString As String
    Dim intNewLine As Integer
    Dim lngLastRow As Long
    Dim lngRow As Long
 '  doesn't work correctly
    Application.ScreenUpdating = False
    Columns("A:B").ClearContents
    strStartString = "target=" & """" & "_blank" & """" & ">"
   
    sURL = "Start"
    Do While sURL <> ""
        sURL = InputBox("Enter search result URL")
    
       'Extract data from website to Excel using VBA
       Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
'       oXMLHTTP.Open "GET", sURL, False
       oXMLHTTP.Open "GET", sURL, False

       oXMLHTTP.send
       sPageHTML = oXMLHTTP.responseText

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
A few things require attention.

1) You must use HTTPS. Without all your code present, unsure if you're doing this correctly.

2) Some data will require you arrange to do a login first, in your code.

3) It's highly unlikely you'll ever get this to work with VBA, because you must implement an entire browser stack which can execute Javascript + potentially CSS.

Fix: Don't use VBA. Do use http://phantomjs.org/ which is a headless (no display) version of Chrome, so you an both view + interact with pages as expected.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
1) I don't understand why showing all the code is necessary since the code I posted is complete as far as it goes and the error occurs in line 26. I've also tried adding my username and password as parameters 4 and 5 in the .Open statement but it didn't help.

2) The same error occurs, logged in or not.

3) I used the code a year or so ago and it worked, so probably EE has made some change that's causing the error.
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
It appears that you must have MSXML3 to have any HTTPS support and even then it is limited.  Experts Exchange is using the latest TLS1.2 certificates.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Author

Commented:
I added references to Microsoft XML, v3.0 and later v6.0 but neither helped.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
1) I don't understand why showing all the code is necessary since the code I posted is complete as far as it goes and the error occurs in line 26. I've also tried adding my username and password as parameters 4 and 5 in the .Open statement but it didn't help.

EE uses Javascript. VBA doesn't provide Javascript support. Code used to access any site using Javascript will fail oddly when code fails to support (be able to run) Javascript.

2) The same error occurs, logged in or not.

See #1.

3) I used the code a year or so ago and it worked, so probably EE has made some change that's causing the error.

Likely Javascript has been added or changed in some way, so Javascript-unaware code (anything except a headless browser) will fail now.

The problem is you never know when a site will start using Javascript.

You must use a headless browser, for your code to work as expected.
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I found a semi-manual way to do what I wanted. Thanks for trying.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial