Excel 2016 32bit VBA Pulling info from Website to Sheet

CrashOD · Feb 21, 2023

VBA Code:

Sub ExtractNameFromWeb()
    Dim htmlDoc As New HTMLDocument
    Dim htmlElements As IHTMLElementCollection
    Dim htmlElement As IHTMLElement
    Dim url As String

    Dim xmlhttp As Object
    Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")

    'Loop through all rows in column F and extract the parcel number from the URL
    For i = 2 To Cells(Rows.Count, 7).End(xlUp).Row
        url = "https://tax.norrycopa.net/taxclaim/trirsp2pp.asp?parcel=" & Cells(i, 7).Value
        
        'Send a request to the web server and receive the HTML response
        Debug.Print "Request URL: " & url
        xmlhttp.Open "GET", url, False
        xmlhttp.send
        htmlDoc.body.innerHTML = xmlhttp.responseText
        'Debug.Print "HTML Response:"
        'Debug.Print htmlDoc.body.innerHTML
        
        'Find the element containing the Name field
        Set htmlElements = htmlDoc.getElementsByTagName("td")
        For Each htmlElement In htmlElements
            If htmlElement.innerText = "NAME: " Then
                Range("B" & i).Value = htmlElement.NextSibling.innerText
                Debug.Print "Name: " & Range("B" & i).Value
            ElseIf htmlElement.innerText = "ADDRESS: " Then
                Range("C" & i).Value = htmlElement.NextSibling.innerText
                Debug.Print "Address: " & Range("C" & i).Value
                
                'Extract city, state, and zip code from the address
                Dim addressParts() As String
                addressParts = Split(Range("C" & i).Value, ", ")
                If UBound(addressParts) >= 1 Then
                    Range("D" & i).Value = Trim(addressParts(0))
                    Debug.Print "City: " & Range("D" & i).Value
                    
                    Dim cityStateZip As String
                    cityStateZip = htmlElement.NextSibling.innerText
                    Dim cityStateZipParts() As String
                    cityStateZipParts = Split(cityStateZip, "&nbsp;")
                    Range("E" & i).Value = Trim(cityStateZipParts(0))
                    Debug.Print "State: " & Range("E" & i).Value
                    Range("F" & i).Value = Trim(cityStateZipParts(1))
                    Debug.Print "Zip Code: " & Range("F" & i).Value
                    Debug.Print "Address: " & htmlElement.NextSibling.innerText
                Else
                    Debug.Print "Address does not contain city and state/zip code"
                End If
            End If
        Next htmlElement
    Next i
End Sub

Codes - With Coding.xlsm

A

B

C

D

E

F

G

1

Tax Claims Update Done on

Tax Claims Name

Tax Claims Address

Tax Claims City

Tax Claims State

Tax Claims Zip

Parcel

2

MENSCH DONALD A/WAGNER CONNIE A

814 N COAL ST

017-01-009-036

Sheet7

HTML:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Home - County of Northumberland</title>
<link rel="shortcut icon" href="/favicon.ico" type="image/x-icon" />
<meta content="County of Northumberland" name="copyright" />
 
</head>
<body>
<!-- end of trihdr.inc -->

<!-- # xnclude Virtual="/taxclaim/inc/BrowserDetect.inc"-->
<!-- # xnclude Virtual="/taxclaim/inc/Rodstyle.inc" -->
<!-- # xnclude Virtual="/taxclaim/inc/TableConfiguration.inc" -->


<!--Script for KB shortcuts -->
     <script type="text/javascript">
    if (document.layers) document.captureEvents(Event.KEYPRESS); // NS4+
    if (document.all || document.layers)  document.onkeypress=pressed; // IE4+ & NS4+
    function pressed(e) {
        var Val='';
        if (document.layers) Val='' + e.which;
        else Val = window.event.keyCode;
                //if B or b is pressed go to back to query
                if ( ((Val == 98) || (Val == 66)) && (typeof(QueryReturn) != "undefined" ) ){
                document.location = QueryReturn;
                }
                //if N or n is pressed go to next page
                if ( ((Val == 110) || (Val == 78)) && (typeof(NextPgKB) != "undefined" ) ){
                document.location = NextPgKB;
                }
                //if P or p is pressed go to previous page
                if ( ((Val == 112) || (Val == 80)) && (typeof(PrevPgKB) != "undefined" ) ) {
                document.location = PrevPgKB;
                }
                //if Q or q is pressed go to a new search
                if ((Val == 113) || (Val == 81)) {
                document.location = "tri.asp";
                }
                //if S or s is pressed view status
                if ( ((Val == 115) || (Val == 83)) && (typeof(StatusKB) != "undefined" ) ) {
                document.location = StatusKB;
                }
        //alert('>'+ String.fromCharCode(Val) + '< pressed, ascii value:  '+Val);
    } 
    </script>
<!-- End Script for KB shortcuts -->
<script language="JavaScript">
function bringfocus(){
    document.focus();
    window.name = "ASSESSMENT";
}
</script>

<script>var QueryReturn="trirsp1.asp?parcel=017-01-009-036&surname=&given=&street=&proptyp=&bsdate=&esdate=&minpric=&maxpric=&minliv=&maxliv=&minacre=&maxacre=&"</script>

<!-- # xnclude Virtual="/taxclaim/inc/RodLink.inc" -->

<div class="claimpage">
<!-- start of main border Table -->
<center>
<table bgcolor="#D3D3D3" style="border: medium solid #D3D3D3;">
<TR><td colspan="2"  valign="top">
<center><h2>Northumberland County Tax Claim Bureau</h2></center>
<!-- start of Top Table (next/prev & keyboard shortcuts) -->

  <!-- top table -->
  <table width="100%" border="0" cellspacing="0" cellpadding="0" align="center" bgcolor="#d3d3d3" style="font: normal 12pxs;">
    <tr>
      <td>
        <!-- start nav table -->
        <table>
          <tr>
            <td colspan=20 align=center style="font: bold 14px;">Search Result 1 of 1</td>
          </tr>
          <tr><Td height=5></TD></tr>
          <tr>
            <td width="10"></td>
            <td align="center" valign="top" bgcolor="#FBF8F0" style="border: outset Gray;" onMouseOver="this.bgColor='#ccccff';" onMouseOut="this.bgColor='#FBF8F0';"><a style="text-decoration: none; font-size: 12px; color: black;" href="trirsp1.asp?parcel=017-01-009-036&surname=&given=&street=&proptyp=&bsdate=&esdate=&minpric=&maxpric=&minliv=&maxliv=&minacre=&maxacre=&"><b>Return to Results</b></a>
            </td>
            <td width="10" ></td>
            <td width=70></td>
            <td width="10"></td>
            <td width=50></td>   
            <td width="10"></td>
            <td bgColor="#FBF8F0" align="center" style="border: outset Gray;" onMouseOver="this.bgColor='#ccccff';"  onMouseOut="this.bgColor='#FBF8F0';"><a href="/taxclaim/tri.asp" style="text-decoration: none; font-size: 12px; color: Black;"><b>New Search</b></a></td>
            <td width="10"></td>
          </tr>
        <!-- end nav table -->
        </table>
      </td>
      <td>
        <table border=1>
        <th colspan=4><font size=-1>Keyboard Shortcuts</font></th>
        <tr><td><font size=-2>N</font></td><td><font size=-2>Next Result</font></td><td><font size=-2>P</font></td><td><font size=-2>Previous Result</font></td></tr>
        <tr><td><font size=-2>Q</font></td><td><font size=-2>New Query</font></td><td><font size=-2>B</font></td><td><font size=-2>Return to Query</font></td></tr>
        <!--<tr><td colspan="4" align="center" style="border: thin Black;"><font size=-2><a href="http://tax.norrycopa.net/taxclaim/trirsp2pp.asp?parcel=017%2D01%2D009%2D036+++++++++++&currentlist=0&printme=YES&" target="_blank">Printable Version</a></font></td></tr>-->
        
        </table>
      </td>
        
    </tr>
  <!-- top table -->
  </table>
    

<!-- end of header and nav, begin parcel info -->

</td></tr>

<tr><td colspan=20><div align=center style="font-family: Arial, Helvetica, sans-serif; font-size: 16px; font-weight: bold;">
<font color="#D3D3D3">THIS IS NOT A CERTIFIED SEARCH
</font>
<br>

Data is current as of 1 AM on (02/17/2023)

<br></div></td></tr>
<tr><td>

<!-- start of General Parcel Information Table -->

<table width="100%" cellspacing="0" cellpadding="0" bgcolor="#FFFFFF" style="font: normal 14px;">
  <tr><Td colspan=4 height=10></TD></tr>
  <tr align=left bgcolor=white onMouseOver="this.bgColor='#ccccff';" onMouseOut="this.bgColor='#FFFFFF';" style="font: bold 16pxs;">
    <td colspan=4 align=center >PARCEL NUMBER: 017-01-009-036           </td></tr>
  <tr align=left bgcolor=white onMouseOver="this.bgColor='#ccccff';" onMouseOut="this.bgColor='#FFFFFF';" >
    <TD colspan=4 align=center>Deed Book - Page:
               2593-93         
    </TD></TR>
  <tr><td colspan=4 height=10></td></tr>
  <tr  align=left bgcolor=white onMouseOver="this.bgColor='#ccccff';" onMouseOut="this.bgColor='#FFFFFF';">
    <TD><b> NAME: </b></TD>
    <td>MENSCH DONALD A/WAGNER CONNIE A   
    </td>
        <!--Right column/row with the last sale info-->
    <td rowspan="10" width=50 bgcolor=white onMouseOver="this.bgColor='#FFFFFF';" onMouseOut="this.bgColor='#FFFFFF';"></td>
    <td rowspan="10" valign="bottom" bgcolor=white onMouseOver="this.bgColor='#FFFFFF';" onMouseOut="this.bgColor='#FFFFFF';">
    </td>
  <!-- End last sale info section-->
  </TR>
 
    <TR align=left bgcolor=white onMouseOver="this.bgColor='#ccccff';" onMouseOut="this.bgColor='#FFFFFF';">
      <TD><b> ADDRESS: </b></TD>
      <td>814 N COAL ST                      </td>
    </TR>
 
    <TR align=left bgcolor=white onMouseOver="this.bgColor='#ccccff';" onMouseOut="this.bgColor='#FFFFFF';" >
      <td></td>
      <td>
    SHAMOKIN                           &nbsp;
    PA&nbsp;
    17872     
      </TD>
    </TR>
 
    <tr align=left bgcolor=white onMouseOver="this.bgColor='#ccccff';" onMouseOut="this.bgColor='#FFFFFF';" >
      <TD ><b> LOCATION: </b></TD>
      <td>810 N COAL ST                      </td>
    </TR>
 
  <tr><Td colspan=2 height=5></TD></tr>
  <tr align=left bgcolor=white onMouseOver="this.bgColor='#ccccff';" onMouseOut="this.bgColor='#FFFFFF';" >
    <td><b> DISTRICT: </b></td>
    <td>01 (SHAMOKIN CITY WARD  9) </TD>
  </tr>
  <tr align=left bgcolor=white onMouseOver="this.bgColor='#ccccff';" onMouseOut="this.bgColor='#FFFFFF';" >
    <TD valign=top><b> DESCRIPTION:</b></td>
    <td>
      LOT 27 X 65                        <br>
    </TD>
  </TR>
  <tr align=left bgcolor=white onMouseOver="this.bgColor='#ccccff';" onMouseOut="this.bgColor='#FFFFFF';" >
    <TD valign=top><b> ASSESSED VALUE:</b></td>
    <td>
      4,550
    </TD>
  </TR>
    
 
<!-- end of General Parcel Information Table -->
</table>

</TD> </TR>



<!-- BEGIN column header row -->
<tr><td bgcolor="white">
 


<!-- start of tax claim detail-line table -->
<style type = "text/css">
  @import "/taxclaim/inc/NEWebiaStyle.css";
</style>
 

<!-- start of tax claim detail-line table -->
<style type = "text/css">
  @import "/taxclaim/inc/NEWebiaStyle.css";
</style>
 

<!-- start of tax claim detail-line table -->
<style type = "text/css">
  @import "/taxclaim/inc/NEWebiaStyle.css";
</style>
 

<!-- start of tax claim detail-line table -->
<style type = "text/css">
  @import "/taxclaim/inc/NEWebiaStyle.css";
</style>
 

<!-- start of tax claim detail-line table -->
<style type = "text/css">
  @import "/taxclaim/inc/NEWebiaStyle.css";
</style>
 

<!-- start of tax claim detail-line table -->
<style type = "text/css">
  @import "/taxclaim/inc/NEWebiaStyle.css";
</style>
 

<!-- start of tax claim detail-line table -->
<style type = "text/css">
  @import "/taxclaim/inc/NEWebiaStyle.css";
</style>
 

<!-- start of tax claim detail-line table -->
<style type = "text/css">
  @import "/taxclaim/inc/NEWebiaStyle.css";
</style>
 
<tr><td align=center>No Delinquent Taxes on file.</td></tr>
<tr><td align=center><font color="red" size="1">CERTIFIED SEARCHES MUST BE MADE THROUGH THE TAX CLAIM OFFICE. PLEASE CALL 570-988-4116.</font></td></tr>

<!-- end of main border table -->
    </td>
  </tr>
</table>
</center>
</div>
<!-- start of tritlr.inc -->
 
</body>
</html>

from excel Immediate WIndow:
Request URL: Home - County of Northumberland
Name: MENSCH DONALD A/WAGNER CONNIE A
Address: 814 N COAL ST
Address does not contain city and state/zip code
Address does not contain city and state/zip code

HTML part were the city state and zip being pull from that is part of the full html uptop:
<TR align=left bgcolor=white onMouseOver="this.bgColor='#ccccff';" onMouseOut="this.bgColor='#FFFFFF';" >
<td></td>
<td>
SHAMOKIN  
PA 
17872
</TD>
</TR>

I can not figure out how to get it. i would rather the City, State Zip in just D column after it does pull that info in. but as you can see it just pulls name and street address.

thanks for any help!

John_w · Feb 22, 2023

Try this:

VBA Code:

Public Sub ExtractNameFromWeb2()

    Dim htmlDoc As New HTMLDocument
    Dim htmlElements As IHTMLElementCollection
    Dim url As String
    Dim i As Long, n As Long
    Dim parts As Variant, city As String, p As Long

    Dim xmlhttp As Object
    Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")

    'Loop through all rows in column F and extract the parcel number from the URL
    For i = 2 To Cells(Rows.Count, 7).End(xlUp).Row
        url = "https://tax.norrycopa.net/taxclaim/trirsp2pp.asp?parcel=" & Cells(i, 7).Value
        
        'Send a request to the web server and receive the HTML response
        Debug.Print "Request URL: " & url
        xmlhttp.Open "GET", url, False
        xmlhttp.send
        htmlDoc.body.innerHTML = xmlhttp.responseText
        'Debug.Print "HTML Response:"
        'Debug.Print htmlDoc.body.innerHTML
        
        Set htmlElements = htmlDoc.getElementsByTagName("td")
        
        For n = 0 To htmlElements.Length - 1
            If htmlElements(n).innerText = "NAME: " Then
                Range("B" & i).Value = htmlElements(n + 1).innerText
                Debug.Print "Name: " & Range("B" & i).Value
            ElseIf htmlElements(n).innerText = "ADDRESS: " Then
                Range("C" & i).Value = htmlElements(n + 1).innerText
                Debug.Print "Address: " & Range("C" & i).Value
                If htmlElements(n + 2).innerText = "" Then
                    Debug.Print htmlElements(n + 3).innerText
                    parts = Split(Application.Trim(htmlElements(n + 3).innerText), " ")
                    city = ""
                    For p = 0 To UBound(parts) - 2
                        city = city & parts(p) & " "
                    Next
                    Range("D" & i).Value = Left(city, Len(city) - 1)
                    Range("E" & i).Value = parts(UBound(parts) - 1)
                    Range("F" & i).Value = parts(UBound(parts))
                End If
            End If
        Next
        
    Next
    
End Sub

CrashOD · Feb 23, 2023

thank you so much. i spent days trying different things and could nor figure it out. I am new at VBA I can figure something out but not everything. Thanks so much!

Excel 2016 32bit VBA Pulling info from Website to Sheet

CrashOD

Board Regular

Excel Facts

John_w

MrExcel MVP

CrashOD

Board Regular

Similar threads

Forum statistics

Share this page

Excel 2016 32bit VBA Pulling info from Website to Sheet

CrashOD

Board Regular

Excel Facts

John_w

MrExcel MVP

CrashOD

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock