Importing data from a web page to Excel via VBA

4

It's the following: I found the code on the internet that works perfectly with what it's indicating, but I need to adapt it to a project and I'm not getting it.

In short, the macro accesses the link page, verifies the member search form, uses the listbox (where it shows the states) as search parameter (of the various fields it has), returns a table with the requested information and imports into Excel's PLAN1. I would just need to change, instead of using the listbox (States), to use the textbox (First Name) of the form. But I can not at all ... could you help me?

Follow the code ready:

   Sub extractTablesData()
 'we define the essential variables

 Dim IE As Object, obj As Object
 Dim myState As String
 Dim r As Integer, c As Integer, t As Integer
 Dim elemCollection As Object


 'add the "Microsoft Internet Controls" reference in your VBA Project indirectly
 Set IE = CreateObject("InternetExplorer.Application")

 'more variables for the inputboxes - makes our automation program user friendly

 myState = InputBox("Enter the city where you wish to work")

 With IE

 .Visible = True
 .navigate ("https://www.namb.org")

 ' we ensure that the web page downloads completely before we fill the form automatically
 While IE.ReadyState <> 4
 DoEvents
 Wend

'accessing the ListBox wit States data
For Each obj In IE.Document.All.Item("csSB_Search_State").Options

        If obj.innerText = myState Then

            obj.Selected = True

        End If

    Next obj

 ' accessing the button

 IE.Document.getElementsByName("Search").Item.Click

 ' again ensuring that the web page loads completely before we start scraping data
 Do While IE.busy: DoEvents: Loop

'Clearing any unnecessary or old data in Sheet1
 ThisWorkbook.Sheets("Sheet1").Range("A1:K500").ClearContents

 Set elemCollection = IE.Document.getElementsByTagName("TABLE")

    For t = 0 To (elemCollection.Length - 1)

        For r = 0 To (elemCollection(t).Rows.Length - 1)
            For c = 0 To (elemCollection(t).Rows(r).Cells.Length - 1)
                ThisWorkbook.Worksheets(1).Cells(r + 1, c + 1) = elemCollection(t).Rows(r).Cells(c).innerText
            Next c
        Next r
    Next t

 End With

 ' cleaning up memory
 Set IE = Nothing

 End Sub

I think I need to make the change in the section below, but I've already tried (almost) everything and I can not:

For Each obj In IE.Document.All.Item("csSB_Search_State").Options

        If obj.innerText = myState Then

            obj.Selected = True

        End If

    Next obj
    
asked by anonymous 26.12.2015 / 22:52

1 answer

4

You only need to replace the block of for that you checked for this:

IE.document.getelementbyid("csSB_Search_FirstName_ID").Value = myState

I used the same variable MyState to not complicate mto, but you can change it if you like.

Probably this question was already, but leave the answer here for future consultations right.

    
05.02.2016 / 12:16