Link to home
Start Free TrialLog in
Avatar of Martin Liss
Martin LissFlag for United States of America

asked on

Examine Experts-Exchange data

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

Avatar of David Favor
David Favor
Flag of United States of America image

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.
Avatar of Martin Liss

ASKER

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.
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.
I added references to Microsoft XML, v3.0 and later v6.0 but neither helped.
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.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial