Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2102
  • Last Modified:

Automate IE webpage with Excel VBA

I am trying to open a web page and insert text into the textboxes and then return the values. I have messed around with the code a little but i am getting no where.  

The url is: http://munstatspa.dced.state.pa.us/FindLocalTax.aspx

I have been trying to assign the value by using this line:
 ie.Document.getElementById("experience-1372700847").Value

Text needs to be entered into the boxes and the submitted.  Then the values in the table need to be retrieved.  

I am a novice at controling IE and have no experience in Java or HTML.  Help is greatly appriciated.

Thank you for your time.
0
AccountantsTech
Asked:
AccountantsTech
  • 6
  • 5
1 Solution
 
MacroShadowCommented:
I'm not sure what "experience-1372700847" is, but the following will automate the website you requested. NOTE: I have not dealt with the returned data.

Sub Demo()
    Dim oIE As Object

    Set oIE = CreateObject("InternetExplorer.Application")

    oIE.Visible = True

    oIE.navigate "http://munstatspa.dced.state.pa.us/FindLocalTax.aspx"

    While oIE.busy And oIE.readystate <> 4
        DoEvents
    Wend

    With oIE.document
        'populate the home address
        .getElementById("txtHomeStreet").Value = "Street Address"
        .getElementById("txtHomeCity").Value = "City"
        .getElementById("igtxttxtHomeZip__ctl1").Value = "Zip 1"
        .getElementById("igtxttxtHomeZip__ctl2").Value = "Zip 2"
        
        'populate the work address
        .getElementById("txtWorkStreet").Value = "Street Address"
        .getElementById("txtWorkCity").Value = "City"
        .getElementById("igtxttxtWorkZip__ctl1").Value = "Zip 1"
        .getElementById("igtxttxtWorkZip__ctl2").Value = "Zip 2"
        
        'click the button
        .Forms(0).Item("btnView").Click
    End With
    
    'Clean-up
    Set oIE = Nothing
End Sub

Open in new window

0
 
AccountantsTechAuthor Commented:
The "experience-1372700847" was just the name that was in code that i found elsewhere on the internet.  How do you find the field names for the text boxex?  "txtHomeStreet".

Do you know how we can get the data from the table that it produces?
0
 
MacroShadowCommented:
Open the webpage in your browser, right click and chose show source. In a separate window you will be shown the html of the page, this king of thing is what you're looking for:
<input name="txtHomeStreet" type="text" id="txtHomeStreet" />

Open in new window

As you can see this belongs to a textbox type="text" whose id is txtHomeStreet id="txtHomeStreet"

The only way I can help you with the results of the query is by seeing the source of the returned results (which I can't do without real info (which you don't want to upload to the public domain!)).
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AccountantsTechAuthor Commented:
Ok that makes a lot more sense.  Thank you for explaining this to this rookie.  Opening the report is working like a charm.

Can't you just put in a trial address and then get the results from that to pull you field names?  As in just use a public address and then i could addapt that later for my system.
0
 
MacroShadowCommented:
It has to be a real address or I will get an error message.
0
 
AccountantsTechAuthor Commented:
Right.  Just use a public place then.  Like the Lancaster Library: 125 N Duke St, Lancaster, PA 17602.  If you use this for both of the address (Home and Work)  you can pull the report with just "dumby" data.
0
 
MacroShadowCommented:
Attached you will find the report.

Unfortunately extracting the information will be complicated as the report is formatted using JavaScript so the elements aren't available to extract their values. It is possible but to save me time, what information do you need?
FindLocalTax2014.pdf
0
 
AccountantsTechAuthor Commented:
The information that i am looking for is:

Home township/boro (Lancaster City (Home))
Home school district (Lancaster S D (Home))
Home PSD code (361001)

Then under the "Total Taxes to be Withheld", both the EIT and the LST rate.

Thank you so much for helping me with this!
0
 
MacroShadowCommented:
This is not the best way, but it is definitely the easiest ;)

All you're interested in is Sub Demo()! Ignore the rest it does the work behind the scenes.

Option Explicit

Sub Demo()

    Dim strURL As String
    Dim arrData() As String
    Dim i As Integer

    strURL = GetUrl("125 N Duke St", "Lancaster", "17602", "125 N Duke St", "Lancaster", "17602")

    arr = GetData(strURL)

'    For i = LBound(arrData) To UBound(arrData)
'        Debug.Print arrData(i)
'    Next

    Debug.Print "Home township/boro: " & arrData(0)
    Debug.Print "Home school district: " & arrData(1)
    Debug.Print "Home PSD code: " & arrData(2)
    Debug.Print "EIT: " & arrData(3)
    Debug.Print "LST: " & arrData(4)

End Sub

Private Function GetUrl(strHomeAddress As String, _
                strHomeCity As String, _
                strHomeZip As String, _
                strWorkAddress As String, _
                strWorkCity As String, _
                strWorkZip As String) As String

    Dim oIE As Object
    Dim oShell As Object
    Dim var As Object
    Dim ie As Object
    Dim strURL As String

    Set oIE = CreateObject("InternetExplorer.Application")

    oIE.Visible = True

    oIE.navigate "http://munstatspa.dced.state.pa.us/FindLocalTax.aspx"

    While oIE.busy And oIE.readystate <> 4
        DoEvents
    Wend

    With oIE.document
        'populate the home address
        .getElementById("txtHomeStreet").Value = strHomeAddress ' "125 N Duke St"
        .getElementById("txtHomeCity").Value = strHomeCity ' "Lancaster"
        .getElementById("igtxttxtHomeZip__ctl1").Value = strHomeZip ' "17602"
        .getElementById("igtxttxtHomeZip__ctl2").Value = ""

        'populate the work address
        .getElementById("txtWorkStreet").Value = strWorkAddress ' "125 N Duke St"
        .getElementById("txtWorkCity").Value = strWorkCity ' "Lancaster"
        .getElementById("igtxttxtWorkZip__ctl1").Value = strWorkZip ' "17602"
        .getElementById("igtxttxtWorkZip__ctl2").Value = ""

        'click the button
        .forms(0).Item("btnView").Click
    End With

    While oIE.busy And oIE.readystate <> 4
        DoEvents
    Wend

'    ' Wait 5 seconds to insure that the report is loaded
'    ' you can change this to suit your internet speed
'    Application.Wait (Now() + CDate("00:00:04"))

    Set oShell = CreateObject("Shell.Application")
    Set ie = CreateObject("InternetExplorer.Application")

    For Each var In oShell.Windows
        If var.LocationName = "Municipal Statistics Report Viewer" Then
            Set ie = var
            Exit For
        End If
    Next var

    ' Get the url for the report
    GetUrl = ie.document.URL
    
    ' Close the instances of IE that we opened
    ie.Quit
    oIE.Quit

    'Clean-up
    Set oIE = Nothing
    Set oShell = Nothing
    Set ie = Nothing
    Set var = Nothing
    
End Function

Private Function GetData(strURL As String) As String()
    
    Dim strConnection As String
    Dim arrData(4) As String
    
    strConnection = "URL;" & strURL

    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("MyDataFromWeb").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = "MyDataFromWeb"

    With ActiveSheet.QueryTables.Add(Connection:=strConnection, Destination:=Range("$A$1"))
        .Name = ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "5"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

    'Home township/boro
    arrData(0) = Sheets("MyDataFromWeb").Range("B6")
    'Home school district
    arrData(1) = Sheets("MyDataFromWeb").Range("B7")
    'Home PSD code
    arrData(2) = Sheets("MyDataFromWeb").Range("C6")
    'EIT
    arrData(3) = Sheets("MyDataFromWeb").Range("E12")
    'LST
    arrData(4) = Sheets("MyDataFromWeb").Range("E13")

   GetData = arrData
   
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("MyDataFromWeb").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    If ActiveWorkbook.Connections.Count > 0 Then
        ActiveWorkbook.Connections(ActiveWorkbook.Connections.Count).Delete
    End If

End Function

Open in new window

0
 
AccountantsTechAuthor Commented:
Works like a charm.  I had to work out a few things so that it would work on my machine.  ie. longer wait for slow internet.  

Thank you so much for your help!  It is much appreciated.  I wish that i could repay you somehow but i guess your reward is just knowing that you did your good deed for the day:)

Thanks again and have a great weekend!

God bless.
0
 
MacroShadowCommented:
Glad I could help, that's what I live for:)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now