Solved

Automate IE webpage with Excel VBA

Posted on 2014-02-11
11
1,825 Views
Last Modified: 2014-02-15
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
Comment
Question by:AccountantsTech
  • 6
  • 5
11 Comments
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39851580
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
 

Author Comment

by:AccountantsTech
ID: 39851932
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39852356
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
 

Author Comment

by:AccountantsTech
ID: 39853385
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39853440
It has to be a real address or I will get an error message.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:AccountantsTech
ID: 39853573
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39853954
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
 

Author Comment

by:AccountantsTech
ID: 39854066
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
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39855583
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
 

Author Comment

by:AccountantsTech
ID: 39859221
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39861928
Glad I could help, that's what I live for:)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now