VBA Grab data from webpage into variable

Question

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

Did you try the functions at the end of your posted code? Seems like it would be easier to do this directly in Excel instead of using a web page... — Tim Williams, Dec 01 '13 at 00:36

score 0 · Answer 1 · answered Dec 01 '13 at 00:38

0

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

answered Dec 01 '13 at 00:38

ron

1,456
3
18
27

That's basically the last part of the OP's posted code (but his function returns no result...) – Tim Williams Dec 01 '13 at 01:37
Yes, it is giving the result. I pasted it at the end of the code and my_var is now storing entire body of the webpage. I can go with it. I have to segregate the value inside "Set1" through excel 'search' and 'mid' function and finally store these values in excel cells. My work can be done with this code. But it will be much better if instead of getting the entire body, the values of Set1 can be stored in my_var. Again, I thought about calculating the randomizer calculation in excel itself, but I am not familiar with the calculation they use in their website. Finally, thnx Tim for ur interest – user3051587 Dec 02 '13 at 04:59
just add the following code and "my_results" will contain the the Set1 data `Set Results = IE.document.getelementsbytagname("table") For Each itm In Results If InStr(1, itm.innertext, "set #", vbTextCompare) Then my_results = itm.innertext Exit For End If Next itm` – ron Dec 02 '13 at 16:01

VBA Grab data from webpage into variable

1 Answers1