Scrape data from password protected site without using Internet Explorer

[cfunk]

I am trying to scrape data from Netsuite. The code I have works great, the problem is that Netsuite will soon not be supported on Internet Explorer. How can I run this same code using a different browser? Thanks!

Sub GetTable()

Dim ieApp As InternetExplorer
Dim ieDoc As Object
Dim ieTable As Object
Dim clip As DataObject

'create a new instance of ie
Set ieApp = New InternetExplorer

'you don’t need this, but it’s good for debugging
ieApp.Visible = True

'assume we’re not logged in and just go directly to the login page
ieApp.Navigate "https://system.netsuite.com/pages/customerlogin.jsp?country=US"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set ieDoc = ieApp.Document

'fill in the login form – View Source from your browser to get the control names
With ieDoc.forms(0)
.Email.Value = "email"
.Password.Value = "password"
.submit
End With
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

'now that we’re in, go to the page we want
ieApp.Navigate "https://1305081.app.netsuite.com/app/common/search/searchresults.nl?searchid=1592&whence="
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

'get the table based on the table’s id
Set ieDoc = ieApp.Document
Set ieTable = ieDoc.all.Item("div__bodytab")

'copy the tables html to the clipboard and paste to the sheet
If Not ieTable Is Nothing Then
Set clip = New DataObject
clip.SetText "" & ieTable.outerHTML & ""
clip.PutInClipboard
Sheet1.Select
Sheet1.Range("A1").Select
Sheet1.PasteSpecial "Unicode Text"
End If

'close 'er up
ieApp.Quit
Set ieApp = Nothing

End Sub

 

[Dan_W]

Have you tried using Selenium? It's a way of using VBA to control Chrome / Firefox / Edge in a similar way as it controls IE. Looking at your codes, this seems like something Selenium can do. You should check out the Wise Owl videos on Youtube, and I wrote a short post on how to install it (which was valid at the time): Web Scrape Error : getElementsByClassName

 

[cfunk]

I have not tried Selenium yet. I'm hoping to find a solution where I don't have to open any browser. This is the code that I'm trying to use to login:

Sub GetTable2()

    Dim req As New MSXML2.XMLHTTP60
    Dim url As String
    
    url = "https://1305081.app.netsuite.com/app/common/search/searchresults.nl?searchid=1704&whence="
    
    req.Open "Post", url, False
    req.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    req.send "email=(myemail)&password=(mypassword)"

    'check if page loaded    
    If req.Status <> 200 Then
        MsgBox req.Status & " - " & req.statusText
        Exit Sub
        
    End If
    
    saveHTMFile req.responseText

End Sub

When I get to the point of 'check if page loaded, the message box says "Internal Server Error."

I'm trying to adapt it from this code from Wise Owl, which works perfectly:

Sub POSTReq()

    Dim req As New MSXML2.XMLHTTP60
    Dim url As String
    
    url = "https://www.wiseowl.co.uk/search/start"
    
    req.Open "Post", url, False
    req.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    req.send "what=excel+vba"
    
    'check if page loaded
    If req.Status <> 200 Then
        MsgBox req2.Status & " - " & req.statusText
        Exit Sub
        
    End If
    
    saveHTMLFile req.responseText

End Sub

They both call this sub which save the loaded page as a way to check if everything loaded correctly:

Sub saveHTMFile(HTMLString As String)

    Dim fso As New Scripting.FileSystemObject
    Dim ts As Scripting.TextStream
    
    
    
    Set ts = fso.CreateTextFile( _
        Environ("UserProfile") & "\Desktop\request Output.htm", _
        True, _
        True)
        
    ts.Write HTMLString
    ts.Close
    
End Sub

Any ideas on what could be wrong?

 

[alansidman]

Have you considered using Power Query to Scrape your data. Power Query in O365 is called Get and Transform and is located on the Data Tab of the Ribbon.

 

[cfunk]

Have you considered using Power Query to Scrape your data. Power Query in O365 is called Get and Transform and is located on the Data Tab of the Ribbon.

When I try to login I get this message.

 

[alansidman]

When I try to log in, I get to window that requires a login ID and password. Instead of Anonymous login, you will need to use specific login where you will input your information.

 

[Dan_W]

I'm trying to adapt it from this code from Wise Owl, which works perfectly:

The code from Wise Owl works perfectly for what? For your password protected site?

Do you know what the error number is?

They both call this sub which save the loaded page as a way to check if everything loaded correctly:

SO does the error occur after this? If so, what are the contents of the file it saved?