Website element value copied to Excel cell from Chrome Browser

[yoshimarine]

Let me first say that this process does work when I am going from Excel (2007/2010) to IE 7/8, although it is slow it does the job. Essentially what it does currently is that it takes the address and copies and pastes it into the USPS website to check and see if there is a valid zipcode and then it grabs the values of the address lines, city, state, zip and puts them back into my Excel file. There are 2 issues with this 1. it is slow, about 5 seconds per line, and 2. my company will be sunsetting IE and going with Chrome only in the near future.

I have been able to get Chrome to open to the USPS website, creating the URL search by website, but where I am stuck is how to get the search results out of the USPS site and back into my excel file. I have attempted the "getelementsby..." methods but keep running into an error regarding an object. I am fairly new with regards to HTML, but have some decent experience with VBA coding.

Attached is the entire coding so far. The issue arises as it gets to the "getelementby..." code section. I have tried different parts and pieces. The select case below is the actual code that works with pulling the values from this website when using IE. Any help would be appreciated as I have spent several weeks trying to figure this out on my own, but seem to only confuse myself more and more. Thank you in advance.

Sub g()


    'Dim IEapp As Object
    Dim WebUrl As String
    Dim eRow As Long
    Dim ele As Object
    Dim ie As Object
    Dim myResult As String
    Dim sResult As String
    Dim rAddress As String
    Dim cell As Range
  
Count = 0
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Count = Count + 1
Next


Selection.Replace what:=" ", Replacement:="+", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False






eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


myjobtype = ActiveCell.Value
myapt = ActiveCell.Offset(0, 1).Value
mycity = ActiveCell.Offset(0, 2).Value
mystate = ActiveCell.Offset(0, 3).Value
myzip = ActiveCell.Offset(0, 4).Value
    
    WebUrl = "https://tools.usps.com/go/ZipLookupResultsAction!input.action?resultMode=0&companyName=&address1=" & myjobtype & "&address2=" & myapt & "&city=" & mycity & "&state=" & mystate & "&urbanCode=&postalCode=&zip=" & myzip & """"
    
    'You can't really automate the passing of variables (ie. Login Name and Passwords) using Chrome or FireFox, but the below code will open the desired page
    Shell ("C:\Program Files (x86)\Google\Chrome\Application\chrome.exe -url " & WebUrl)  'Opens URL in Chrome if installed, replace chrome.exe with FireFox
                                                                                            
                                                                                          


Set what = .document.getElementById("address1 range")
what.Item(0).Value = myjobtype
Set apt2 = .document.getElementById("city range")
apt2.Item(0).Value = myapt
Set city = .document.getElementsByName("tCity")
city.Item(0).Value = mycity
Set citystate = .document.getElementsByName("sState")
citystate.Item(0).Value = mystate
Set ZIPCODE = .document.getElementsByName("zip")
ZIPCODE.Item(0).Value = myzip


'Next ele


'For Each ele In .document.all
'Select Case ele.classname


'Case "address1 range"
'ActiveCell.Offset(0, 5).Value = ele.innerText


'Case "city range"
'ActiveCell.Offset(0, 6).Value = ele.innerText


'Case "state range"
'ActiveCell.Offset(0, 7).Value = ele.innerText


'Case "zip"
'ActiveCell.Offset(0, 8).Value = ele.innerText


'Case "zip4"
'ActiveCell.Offset(0, 9).Value = ele.innerText


'End Select


'Next ele
                                                                                 
End Sub