filling web forms and importing the results

[r.baldwin7960]

I work for AT&T,

We sell a service called U-verse, it's a TV service provided through fiber optic cables to your home. I have customers coming into my store asking if the service is available at their address. I check their address or phone number against the online eligiblity checker, and report back to them the results. In the case that they are not available, I add their name, number, and address to a list that I go back and check once a week, so that I can call them when it does become available. Over time, this list has gotten quite large.

What I'm looking for is something that, at the push of a button, will take data from my list, input it into the online tool, and report back to me the results.

Possible results are:

1. "Congratulations" (It's available aka: green)
2. "Thank you for your interests" (It's not available aka: red)
3. (more info needed due to various reasons aka: undecided)

Is there something out there, hopefully excel based (since that is what my list is on), that can do this?

Thank you again in advance for your help!

 

[Legacy 98055]

Is there something out there, hopefully excel based (since that is what my list is on), that can do this?

Yes. How is your data layed out? I'm asking because the example I'm cooking up will fit much more nicely if I know where you data is.

How is your list setup to represent these? What range? Sheet name?

*Required.

*ATTPhoneNumber
*CustomerType

and/or

*Address1
Address2
*City
*State
*Zip
*CustomerType

I'll get back to you after you reply...

 

[r.baldwin7960]

Thank you so much for your help.

My list is setup very basic. It's still named Sheet1. Labels run across row 1, and all of the data is in the following rows. The columns are labeled as follows:

A - Date (Date that I input the entry)
B - Wireless (Customers AT&T cell phone number)
C - Landline (Customers AT&T home phone number)
D - Service Address (Currently the whole address is in a single cell, I can seperate this if needed)
E - Customer Name (Self explanatory)
F - Contact Info (How they would like me to reach them when U-verse becomes "green")

Again, very basic. Columns B and C are formatted in "(xxx) xxx-xxxx" style, in some cases a number isn't available, which is denoted by "n/a".

I would always prefer to check based on their AT&T home phone number before their address, it is usually more reliable as a result.

If there is anything else I can provide, please let me know!

On a side note, I would also like to have a button that opens a form to input new customers in, but this isn't necessary, and I can probably figure it out on my own with time.

 

[Legacy 98055]

<a href="http://home.fuse.net/tstom/0704080127.1613591.zip"><img src="http://home.fuse.net/tstom/zip.gif"width="48"height="48"border="0"></a> <a href="http://home.fuse.net/tstom/0704080127.1613591.zip">0704080127.1613591.zip</a>

Please see the comments in A1, B1,and C3 of the example workbook. Run the example to see how your data will look. The first two rows are available, the third is not, and the fourth could not be resolved automatically. You will need to split up your data though this should not be much of a chore. The site is expecting phone numbers in this format. "##########".

As with any application using automation to gather data from one or more websites, it is at the mercy of the website(s). If the structure of the site is changed, even slightly in some cases, the code may break. If this falters at some point, just send me an email of revise the post at Mr Excel. Usually, a slight adjustment will resolve any problems.

UserForm1 containing WebBrowser1: No code in userform.

Code in thisworkbook class:

Option Explicit

Public Enum AvailabilityStatus
    NotAvailable = 1
    Available = 2
    Undetermined = 3
    UserConfirm = 4
End Enum

Public Enum CustomerSubType
    HomeResidential = 1
    HomeOffice = 2
End Enum

Private WithEvents Browser As WebBrowser
Private uf As UserForm1
Private DocumentComplete As Boolean

Private Sub Browser_DocumentComplete(ByVal pDisp As Object, URL As Variant)
    DocumentComplete = True
End Sub

Private Sub Workbook_Open()
    CreateReferences
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ReleaseReferences
End Sub

Private Sub CreateReferences()
    Set uf = New UserForm1
    Set Browser = uf.WebBrowser1
End Sub

Private Sub ReleaseReferences()
    On Error Resume Next
    Set Browser = Nothing
    Unload uf
    Set uf = Nothing
End Sub

Private Sub WaitUntilReady(Optional TimeOutSeconds As Integer = 10)
    DocumentComplete = False
    Do Until DocumentComplete And Browser.ReadyState = READYSTATE_COMPLETE And Not Browser.Busy
        DoEvents
    Loop
End Sub

'if ATTPhoneNumber is not blank, all address information is ignored
Friend Function ReturnStatus( _
    Optional ATTPhoneNumber As String, _
    Optional Address1 As String, _
    Optional Address2 As String, _
    Optional City As String, _
    Optional State As String, _
    Optional Zip As String, _
    Optional CustomerType As CustomerSubType _
    ) As AvailabilityStatus
    
    Dim Response As String
    Dim StrCustomerType As String
    Dim Document As HTMLDocument
    Dim SearchText As String
    Dim Form2 As HTMLFormElement
       
    On Error GoTo ErrReturnStatus

    If Browser Is Nothing Then
        ReleaseReferences
        CreateReferences
    End If
       
'    uf.Show vbModeless
    
    Do Until Browser.LocationName = "AT&T U-verse(SM) - Check Availability" Or Browser.LocationURL = "https://uverse1.att.com/un/launchAMSSNotAuthenticated.do?target_action=serviceabilityCheck"
        Browser.Navigate "https://uverse1.att.com/un/launchAMSSNotAuthenticated.do?target_action=serviceabilityCheck"
        WaitUntilReady
    Loop
    
    Set Document = Browser.Document
    Set Form2 = Document.forms(2)
    
    If ATTPhoneNumber = "" Then
        'clicks "Use My Address"
        Document.getElementById("notCust").Click
        Document.getElementById("addressLine").Value = Address1
        Document.getElementById("addressLine2").Value = Address2
        Document.all("city").Value = City
        Document.getElementById("states.code").Value = State
        Document.all("zip").Value = Zip
        If CustomerType = 1 Then
            Document.all("customerSubType")(0).Click
        Else
             Document.all("customerSubType")(1).Click
        End If
        Document.getElementById("subButton").Click
    Else
        
        
        If CustomerType = 1 Then
            Document.all("customerSubType")(0).Click
        Else
             Document.all("customerSubType")(1).Click
        End If
        DocumentComplete = False
        Document.getElementById("subButton").Click
    End If
    
    WaitUntilReady
    Set Document = Browser.Document
    Response = Document.body.parentElement.outerHTML
    DocumentComplete = False
    Browser.GoBack
    WaitUntilReady
    
    If InStr(Response, "service is not available") <> 0 Then
        ReturnStatus = NotAvailable
    ElseIf InStr(Response, "exact address") Then
        ReturnStatus = UserConfirm
    ElseIf InStr(Response, "Confirm Service Address") Then
        ReturnStatus = Available
    Else
        ReturnStatus = Undetermined
    End If
   
   Exit Function
ErrReturnStatus:
'error
End Function

The following code is customized for the OP but may be of use as an example of how to call the function and to cancel the query. See the download.
Code is in Sheet1 which contains several commanbuttons and the data.

Option Explicit

Private Cancel As Boolean

Private Sub CommandButton1_Click()
    Dim r As Range, LookBack As Long, QueryThisRow As Boolean
    
    On Error GoTo ErrCommandButton1_Click
    
    Set r = [c4]
    LookBack = InputBox("Only query rows that have not yet been searched or have not been checked for X number of days.", "Enter the number of days that should elapse between searches.", 14)
    
    Do Until r = ""
        If r.Offset(, -2).Value <> 2 Then
            If IsDate(r.Offset(, -1).Value) Then
                If DateDiff("d", r.Offset(, -1).Value, Date) > LookBack Then QueryThisRow = True
            Else
                QueryThisRow = True
            End If
        End If
        
        If QueryThisRow Then
            If r.Offset(, 2).Value Like "##########" Then
                r.Offset(, -2).Value = ThisWorkbook.ReturnStatus(r.Offset(, 2).Value, CustomerType:=r.Offset(, 8).Value)
            Else
                r.Offset(, -2).Value = ThisWorkbook.ReturnStatus(, r.Offset(, 3).Value, r.Offset(, 4).Value, _
                  r.Offset(, 5).Value, r.Offset(, 6).Value, r.Offset(, 7).Value, r.Offset(, 8).Value)
            End If
            r.Offset(, -1).Value = Now
        End If
        
        Set r = r.Offset(1)
        DoEvents
        If Cancel Then
            Cancel = False
            MsgBox "Procedure canceled."
            Exit Sub
        End If
    Loop
    
    MsgBox "Save your workbook..."
Exit Sub
ErrCommandButton1_Click:
    MsgBox Err.Description
End Sub

Private Sub CommandButton2_Click()
    Cancel = True
End Sub