VBA Data Query to Cell

Bobbith · Aug 31, 2018

I'm trying to pull in data from an online lookup site when the user puts in new data in the "zipcode" cell in excel. I feel like I'm fairly close, I just can't figure out how to finish the code out.

Here's the code in excel

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = Range("ZIPCODE").Row And _
Target.Column = Range("ZIPCODE").Column Then

'Declarations
Dim IE As New InternetExplorer

'Navigate
    IE.Visible = True
    IE.navigate "REDACTED/ZIP_CODE=" & Range("ZIPCODE").Value
  

'Loop until Loaded
    Do
    DoEvents
    Loop Until IE.readyState = READYSTATE_COMPLETE
    Dim doc As HTMLDocument
    Set doc = IE.document
  
  
'Data Pull
    Dim sTR As String
    sTR = Trim(doc.getElementsByClassName("TD")(2).innerText)
    Dim aTR As Variant
    aTR = Split(sTR, ",")
    Range("TAXCODE").Value = aTR(2)


End If

End Sub

Here's the code on the website I'm trying to pull data from, CA31 being the text I'm trying to pull.

Code:

<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<meta http-equiv="X-UA-Compatible" content="IE=EmulateIE7">
<title>Ferguson Tax Code Search by Zipcode</title>
<link rel="stylesheet" href="assets/html/LocationsSearch.css" type="text/css">

<style type="text/css">
td {

    /* css-3 */
    white-space: -o-pre-wrap;
    word-wrap: break-word;
    white-space: pre-wrap;
    white-space: -moz-pre-wrap;
    white-space: -pre-wrap;
}

table {
  table-layout: fixed;
  width: 100%
}

</style>


</head>

<body bgcolor="#FFFFFF" text="#000000">
    <table border="0" cellspacing="0" cellpadding="0" align="center" width="100%">
        <tbody><tr>
            <td><img src="assets/images/Ferguson-Color.gif" width="259" height="45" alt="Ferguson"></td>
            <td valign="bottom">
                <div align="right">
                    <b class="TitleHead">Tax Code Search By Zipcode</b> <br> <br>
                    <br>
                </div>
            </td>
        </tr>
        <tr>
            <td><br></td>
        </tr>

        <tr>
            <td colspan="2">
                <table width="100%" cellpadding="3" cellspacing="0" border="1" name="inside form" align="center">
                    <tbody><tr>
                        <td bgcolor="#003366" bordercolor="white" valign="top" align="left" width="200"><font color="white">City</font></td>
                        <td bgcolor="#003366" bordercolor="white" valign="top" align="left" width="200"><font color="white">County</font></td>
                        <td bgcolor="#003366" bordercolor="white" valign="top" align="left" width="200"><font color="white">Tax Code</font></td>
                        <td bgcolor="#003366" bordercolor="white" valign="top" align="left" width="200"><font color="white">Rate</font></td>
                        <td bgcolor="#003366" bordercolor="white" valign="top" align="left" width="200"><font color="white">Wholesale Tax Code</font></td>
                        <td bgcolor="#003366" bordercolor="white" valign="top" align="left" width="450"><font color="white">Notes</font></td>
                    </tr>
                    <tr>
                      
                          
                          
                              
                                    </tr><tr bordercolor="#003366">
                                        <td bordercolor="#003366">ROSEVILLE</td>
                                        <td bordercolor="#003366">PLACER</td>
                                        <td bordercolor="#003366">CA31</td>
                                        <td bordercolor="#003366">7.25</td>
                                        <td bordercolor="#003366">N/A</td>
                                        <td bordercolor="#003366">N/A</td>
                                    </tr>

VBA Data Query to Cell

Bobbith

New Member