VBA to Pull Data from Log in Website

Hello. I am new to excel program and am experience some issues. I am attempting to use excel to log in to a web page and navigate to the correct sub page before copying a table and pasting it into excel.

The issues I am having are below:

1) The first selection is in a drop down box.

2) It opens up a new window you enter user name

3) It needs to log out after finished

I have wrote some code that works using sendkeys... but there has to be a better way of using code to find the specified drop down list, text box, or table on a webpage.

Private Sub Workbook_Open()

Dim appIE As Variant

Set appIE = CreateObject("InternetExplorer.Application")

appIE.Visible = True

appIE.navigate "xxxxxxx"

Application.Wait Now + TimeValue("00:00:02")
   
SendKeys "{TAB 18}", True
SendKeys "{DOWN 1}", True
SendKeys "{TAB 6}", True
SendKeys "xxxxx", True
SendKeys "{ENTER}", True
Application.Wait Now + TimeValue("00:00:03")
SendKeys "xxxxx", True
SendKeys "{ENTER}", True
Application.Wait Now + TimeValue("00:00:08")
SendKeys "{TAB 14}", True
SendKeys "{ENTER}", True
Application.Wait Now + TimeValue("00:00:05")
SendKeys "^a", True
SendKeys "^c", True

 ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
    False, NoHTMLFormatting:=True

End Sub

You can use the HTML Document object to get data from particular elements in a webpage. For example, my code below searches for "tomato" in Google then puts the 10 results into a spreadsheet.

You need to add references to Microsoft Internet Controls and Microsoft HTML Object Library.

Sub domExample()

    Dim oBrowser As New InternetExplorer
    Dim oCollection As IHTMLElementCollection
    Dim oDoc As HTMLDocument
    
    'Open browser, load page and set HTML document
    With oBrowser
        .Visible = False
        .Silent = True
        .navigate "https://www.google.com/search?q=tomato"
        Do While .readyState <> 4
        Loop
        Set oDoc = .document
    End With
    
    'Put result titles in column 1
    Set oCollection = oDoc.getElementsByClassName("r")
    For i = 0 To 9
        Cells(i + 1, 1).Value = oCollection.Item(i).innerText
    Next i
    
    'Put results URLs in column 2
    Set oCollection = oDoc.getElementsByClassName("kv")
    For i = 0 To 9
        Cells(i + 1, 2).Value = oCollection.Item(i).innerText
    Next i
    
    'Quit
    oBrowser.Quit
    Set oBrowser = Nothing
    Set oCollection = Nothing
    Set oDoc = Nothing
    
End Sub

Also, you can use it to manipulate input elements on a page. For example, if you know there is an input box with id "user", and a button with id "submit", you could do the following:

oDoc.getElementById("user").setAttribute "value", "ThisIsMyUserName"
oDoc.getElementById("submit").click

You can also get elements by Name or Class name. To find id's and classes of things on the page just look at the web page source, the F12 Inspect functionality in Google Chrome is really useful for this, I think Firefox has something similar.

Does this help?

Did you add the Microsoft HTML Object Library reference in Tools > References?

I gave an example of how to manipulate the elements:

oDoc.getElementById("user").setAttribute "value", "ThisIsMyUserName"

That will find an input on the page with id "user" and type "ThisIsMyUserName" in that box. (You can fill in your user/pass with this)

oDoc.getElementById("submit").click

That will find an input on the page with id "submit" and Click it. (You can click the login button with this)

Which website are you doing this on?

Yeah, looks like you have the wrong id. You're looking for the "input" (buttons/textboxes) and "select" (dropdowns) elements to manipulate.

Ok, so I've changed the code which will allow you to make your dropdown selections and type in your Access ID and submit the form, but, this opens a new window where you enter your password. So I had to add some code which finds the new window and then lets you enter your password and submit that. I'm using Windows 7 and Internet Explorer 10 and this works for me, but I think it you're might behave differently based on your Windows/Internet Explorer version.

Try it out, if it fails let me know your Windows and Internet Explorer version.

Sub Test_1()

Dim oBrowser As New InternetExplorer
Dim oCollection As IHTMLElementCollection
Dim oDoc As HTMLDocument
Dim oSWindows As New ShellWindows
    
    'Open browser, load page and set HTML document
    With oBrowser
        .Visible = True
        .Silent = True
        .navigate "https://www.scbtonline.com"
        Do While .readyState <> 4
        Loop
        Set oDoc = .document
    End With
    
    
    With oDoc
        .getElementById("ddlService").setAttribute "value", "personal" 'Select service (options below)
            'personal" - Online Banking
            'business" - Business Link
            'billpay - Business BillPay
            'creditcards - Credit Cards
            'brokerage - Brokerage Account
            'assetTrust - Asset Mgt Acct
            'remoteRemote - Deposit Capture
    
        .getElementById("PersonalDestination").setAttribute "value", "SUMMARY" 'Select page (options below)
            'SUMMARY - Summary Page
            'EXPRESSXFR - Express Transfer
            'PRIMARYACCTSUMMARY - Primary Account
            'STOPPAY - Stop Payment
            'OPTIONS - Options
            'NICKNAMES - Change Nicknames
            'SEARCH - Transaction Search
            'EXPORT - Transaction Export
    
        .getElementById("AccessIDVisible").setAttribute "value", "PutYourIDHere" 'Enter Access ID
        .getElementsByClassName("secureloginbtn").Item(0).Click 'Submit
    End With
    
    'Close current tab
    oBrowser.Quit
    Application.Wait Now() + TimeValue("00:00:05")
    
    'Find new window
    For Each oBrowser In oSWindows
        Do While oBrowser.readyState <> 4
        Loop
        If Left(oBrowser.document.URL, 35) = "https://web3.secureinternetbank.com" Then
            Exit For
        End If
    Next oBrowser
    Set oDoc = oBrowser.document
    
    'Type password and submit
    With oDoc
        .getElementById("Password").setAttribute "value", "PutYourPasswordHere" 'Password
        .getElementById("submit1").Click 'Submit
    End With

End Sub

What actually happens when it gets to that line? It throws an error?

Also, setting values for the elements like this won't trigger the visual changes you see when you use the page, but it will make the necessary changes to the form and submit the correct values when the form is submitted.

See here for HTML reference if it helps: http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx

I've also added 1 extra line to the code in my last post so please use that code.