VBA for calling back a webtable

[aabrahamson]

Hi I am rather new to this and have written a VBA by following some directions to call back a specific table data from a webpage, but I would like to be able to do this for multiple rows at a time not just one. can any one help. Here is what I have so far

My sheet is setup to have a EIN column and a Name column,

+++++++++++++++++++++++++++++++++++++++++++++++++++

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Range("EIN").Row And _
Target.Column = Range("EIN").Column Then

Dim IE As New InternetExplorer
IE.Visible = True
IE.navigate "http://apps.irs.gov/app/eos/pub78Search.do?names=&city=&state=All...&country=US&deductibility=all&dispatchMethod=searchCharities&submitName=Search&ein1=" & Range("EIN").Value
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim sDD As String
sDD = Trim(Doc.getElementsByTagName("tr")(3).innerText)
IE.Quit
Dim aDD As Variant
aDD = Split(sDD, ":")
Range("Name").Value = aDD(0)




End If
End Sub

 

[John_w]

Get a reference to the table (e.g. using getElementsByTagName or getElementById, etc.) and then:

    Dim table As HTMLTable
    Dim tRow As HTMLTableRow, tCell As HTMLTableCell
    Dim destinationCell As Range
    
    Set destinationCell = Range("A1")
    For Each tRow In table.Rows
        For Each tCell In tRow.Cells
            destinationCell.Offset(tRow.RowIndex, tCell.cellIndex).Value = tCell.innerText
        Next
    Next

The above code requires a reference to MS HTML Object Library in the VBA project (Tools - References).

 

[aabrahamson]

Hi John,

Thanks for the reply sorry I am very new to this
I am trying this but with no luck I am sure I am missing something simple.

+++++++++++++++++++++++++++++++++
Private Sub getElementsByTagName()




Dim table As HTMLTable
Dim tRow As HTMLTableRow, tCell As HTMLTableCell
Dim destinationCell As Range

Set destinationCell = Range("A1")
For Each tRow In table.Rows
For Each tCell In tRow.Cells
destinationCell.Offset(tRow.RowIndex, tCell.cellIndex).Value = tCell.innerText
Next
Next


Dim IE As New InternetExplorer
IE.Visible = True
IE.navigate "http://apps.irs.gov/app/eos/pub78Search.do?names=&city=&state=All...&country=US&deductibility=all&dispatchMethod=searchCharities&submitName=Search&ein1=" & Range("A1").Value
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim sDD As String
sDD = Trim(Doc.getElementsByTagName("tr")(3).innerText)
IE.Quit
Dim aDD As Variant
aDD = Split(sDD, ":")
Range("B1").Value = aDD(0)
End If
End Sub

+++++++++++++++++++
References for VBA Project:

Microsoft HTML Object Library
Microsoft Internet Controls

Thanks again for any help
Drew

 

[John_w]

You need to put my code after your code, having got a reference to the HTML table containing the data.

What is the full URL, including the A1 cell value? This code uses the URL you posted but without the ein1= parameter value and extracts the first results page.

Public Sub Get_Data()

    Dim IE As InternetExplorer
    Dim HTMLdoc As HTMLDocument
    Dim table As HTMLTable
    Dim tRow As HTMLTableRow, tCell As HTMLTableCell
    Dim destinationCell As Range
    
    With Worksheets("Sheet1")
        .Cells.ClearContents
        Set destinationCell = .Range("A1")
    End With
    
    Set IE = New InternetExplorer
    With IE
        IE.Visible = True
        IE.navigate "http://apps.irs.gov/app/eos/pub78Search.do?names=&city=&state=All...&country=US&deductibility=all&dispatchMethod=searchCharities&submitName=Search&ein1"
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        Set HTMLdoc = .document
    End With
    
    Set table = HTMLdoc.getElementsByTagName("TABLE")(2)
    
    For Each tRow In table.Rows
        For Each tCell In tRow.Cells
            destinationCell.Offset(tRow.RowIndex, tCell.cellIndex).Value = tCell.innerText
        Next
    Next
   
End Sub

 

[aabrahamson]

Here is the EIN number I was using as a test
221487247
here is the result I got back
22-1487247United Way of Northern New Jersey Inc.MorristownNJ United States PC

 

[aabrahamson]

Sorry here is the full URL call
Exempt Organizations Select Check

 

[aabrahamson]

OK I can get it to give me the table if I put the EIN value that works great but I guess what I was looking for was to be able to populate a table based on the EIN for example have all the EIN values in column A and then return there table value below. Thank you again for all your help so far
[TABLE="width: 545"]
<colgroup><col><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 721"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]EIN [/TD]
[TD] EIN [/TD]
[TD]Legal Name[/TD]
[TD]City [/TD]
[TD]State [/TD]
[TD]Country [/TD]
[TD]Deductibility Status [/TD]
[/TR]
[TR]
[TD="align: right"]135613797[/TD]
[TD] 13-5613797[/TD]
[TD]American Heart Association Inc.[/TD]
[TD]Dallas[/TD]
[TD]TX [/TD]
[TD]United States[/TD]
[TD] PC [/TD]
[/TR]
[TR]
[TD="align: right"]222180775[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]226063974[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]