VBA Grab data from webpage into variable

5.6k views Asked by At

I am able to pass the excel values to the website and click it through vba. But it opens up another page with title "Results - Research Randomizer" and I dont know how I retrieve those values inside "Set#1". Can anyone give me some idea to retrieve those values into a variable. My code is

Sub OpenPage()
Const myPageTitle As String = "Research Randomizer Form v4.0"
Const myPageURL As String = "http://www.randomizer.org/form.htm"
    Dim NoofSet, NoPSet, RangeBeg, RangeEnd As String
    NoofSet = Range("b3").Value
    NoPSet = Range("c3").Value
    RangeBeg = Range("d3").Value
    RangeEnd = Range("e3").Value

    Dim myIE As SHDocVw.InternetExplorer
    Dim doc As HTMLDocument
    Dim PageForm As HTMLFormElement
    Dim UserIdBox As HTMLInputElement
    Dim PasswordBox As HTMLInputElement
    Dim HrangeBeg, HrangeEnd As HTMLInputElement
    Dim FormButton As HTMLInputButtonElement
    Dim Elem As IHTMLElement


    'check if page is already open
  Set myIE = GetOpenIEByTitle(myPageTitle, False)
     If myIE Is Nothing Then
    'page isn't open yet
    'create new IE instance
    Set myIE = GetNewIE
    'make IE window visible
    myIE.Visible = True
    'load page
    If LoadWebPage(myIE, myPageURL) = False Then
      'page wasn't loaded
      MsgBox "Couldn't open page"
      Exit Sub
    End If
  End If

    Do
    DoEvents
    Loop Until myIE.readyState = READYSTATE_COMPLETE

    Set doc = myIE.document
    Set PageForm = doc.forms(0)
    'Get the User Id textbox
    '< input class="TextBox" maxlength="15" name="UserName" size="12">

    Set UserIdBox = PageForm.elements("numofsets")
    'Set the User Id
    UserIdBox.Value = NoofSet

    'Get the password textbox
    '< input class="TextBox" type="password" maxlength="10" name="Password" size="12">
    Set PasswordBox = PageForm.elements("numperset")
    'Set the password
    PasswordBox.Value = NoPSet

    Set HrangeBeg = PageForm.elements("rangebeg")
    HrangeBeg.Value = RangeBeg
    Set HrangeEnd = PageForm.elements("rangeend")
    HrangeEnd.Value = RangeEnd


    'Submit the form (like clicking the 'Submit' button) to navigate to next page

    PageForm.Button.Click

    'Wait for the new page to load

    Do
    DoEvents
    Loop Until myIE.readyState = READYSTATE_COMPLETE
    myIE.Visible = True
'Working fine till here
'Need to pull the data from the 2nd webisite





    End Sub

'returns new instance of Internet Explorer
Function GetNewIE() As SHDocVw.InternetExplorer
  'create new IE instance
  Set GetNewIE = New SHDocVw.InternetExplorer
  'start with a blank page
  GetNewIE.Navigate2 "about:Blank"
End Function

'loads a web page and returns True or False depending on
'whether the page could be loaded or not
Function LoadWebPage(i_IE As SHDocVw.InternetExplorer, _
                     i_URL As String) As Boolean
  With i_IE
    'open page
    .navigate i_URL
    'wait until IE finished loading the page
    Do While .readyState <> READYSTATE_COMPLETE
      Application.Wait Now + TimeValue("0:00:01")
    Loop
    'check if page could be loaded
    If .document.URL = i_URL Then
      LoadWebPage = True
    End If
  End With
End Function

'finds an open IE site by checking the URL
Function GetOpenIEByURL(ByVal i_URL As String) As SHDocVw.InternetExplorer
Dim objShellWindows As New SHDocVw.ShellWindows

  'ignore errors when accessing the document property
  On Error Resume Next
  'loop over all Shell-Windows
  For Each GetOpenIEByURL In objShellWindows
    'if the document is of type HTMLDocument, it is an IE window
    If TypeName(GetOpenIEByURL.document) = "HTMLDocument" Then
      'check the URL
      If GetOpenIEByURL.document.URL = i_URL Then
        'leave, we found the right window
        Exit Function
      End If
    End If
  Next
End Function

'finds an open IE site by checking the title
Function GetOpenIEByTitle(i_Title As String, _
                          Optional ByVal i_ExactMatch As Boolean = True) As SHDocVw.InternetExplorer
Dim objShellWindows As New SHDocVw.ShellWindows

  If i_ExactMatch = False Then i_Title = "*" & i_Title & "*"
  'ignore errors when accessing the document property
  On Error Resume Next
  'loop over all Shell-Windows
  For Each GetOpenIEByTitle In objShellWindows
    'if the document is of type HTMLDocument, it is an IE window
    If TypeName(GetOpenIEByTitle.document) = "HTMLDocument" Then
      'check the title
      If GetOpenIEByTitle.document.Title Like i_Title Then
        'leave, we found the right window
        Exit Function
      End If
    End If
  Next
End Function
1

There are 1 answers

3
ron On

This code will find and identify the open "Results" window and then assign the source code behind it to a variable (my_var). You can then extract what you want from the variable.

' Find the open instance of IE that contains the "Results"
    Set objShell = CreateObject("Shell.Application")
    IE_count = objShell.Windows.Count

    For x = 0 To (IE_count - 1)
        On Error Resume Next
        my_url = objShell.Windows(x).document.Location
        my_title = objShell.Windows(x).document.Title

        If my_title Like "Results - Research Randomizer" Then
            Set ie = objShell.Windows(x)
            Exit For
        Else
        End If
    Next

    my_var = ie.document.body.innerhtml