Home > Forum Home > Automating Data Analysis Tasks > How to pull data from webpage(.aspx) for a excel column? Share

How to pull data from webpage(.aspx) for a excel column?

Excel Help for How To Pull Data From Webpage(.aspx) For A Excel Column? in Automating Data Analysis Tasks


Forum TopicPost Reply Login

How To Pull Data From Webpage(.aspx) For A Excel Column?

Rate this:
(3/5 from 1 vote)
ConfusedHi, I am new here. I need help to write macro/code which can submit query in the application status feedbox at http://124.124.193.235/eregister/eregister.aspx and submit. From the results, i want to copy STATUS data to excel sheet. For example query numbers are 1274032, 1460986, 1522002 in the excel column. Please help me.
 Posted by on
 
Replies - Displaying 1 to 2 of 2Order Replies By: Most recent | Chronological | Highest Rated
ConfusedHello friends,

VBA Code:
Private Sub WebBrowser1_StatusTextChange(ByVal Text As String)

End Sub

Sub Pull_Status()
Dim iLastRow As Integer
Dim Rng As Range
Dim Txt As String

Me.WebBrowser1.navigate "http://124.124.193.235/eregister/viewdetails.aspx"

While Me.WebBrowser1.Busy Or Me.WebBrowser1.ReadyState <> 4
DoEvents
Wend

iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
For Each Rng In Range("a2:a" & iLastRow)
If Not Rng.Value = vbNullString Then
Rng.Select
Me.WebBrowser1.Document.getElementById("applNumber").Value = Rng.Value
Me.WebBrowser1.Document.getElementById("btnView").Click
x = vbNullString
Do
On Error Resume Next
x = Me.WebBrowser1.Document.getElementsByTagName("td")(0).innerHTML
On Error GoTo 0
DoEvents
Loop While x <> "(NOT FOR LEGAL USE)"
For i = 0 To Me.WebBrowser1.Document.getElementsByTagName("td").length
If Me.WebBrowser1.Document.getElementsByTagName("td")(i).innerHTML Like "*Status*" Then
Txt = Me.WebBrowser1.Document.getElementsByTagName("td")(i).innerHTML
Exit For
End If
Next
Txt = StripHTML(Txt)
Txt = Replace(Txt, " ", vbNullString)
Txt = Replace(Txt, "Status :", vbNullString)

ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Txt
Me.WebBrowser1.navigate "http://124.124.193.235/eregister/viewdetails.aspx"
While Me.WebBrowser1.Busy Or Me.WebBrowser1.ReadyState <> 4
DoEvents
Wend

End If
Next Rng

End Sub

Thanks a lot
Kynthia Melissa
 Posted by on
Oops
Rate this:
(3/5 from 1 vote)
You need to post the variables to the destination form page within the web query.  There is a post about doing this here.

From the source HTML of the page that you indicate the detination web page is 'viewdetails.aspx' and the form name for the application number is 'applNumber'.
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for How to pull data from webpage(.aspx) for a excel column? in the