Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

ppkg2's Avatar
ppkg2 ppkg2 is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
28-Jun-2012, 07:35 AM #1
Solved: How to pull data from webpage(.aspx) for a excel column?
Hi, 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 click on 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.

For 1274032 the result page shows STATUS as REGISTERED. I want REGISTERED copied to adjacent cell of 1274032 cell

Please help me.

Last edited by ppkg2; 28-Jun-2012 at 07:45 AM..
valis's Avatar
Moderator with 63,266 posts.
 
Join Date: Sep 2004
Location: as above
28-Jun-2012, 08:31 AM #2
I don't understand. Is that a trade-mark page? What are you trying to accomplish by writing a macro for this? Are you just trying to register a bunch of times?
ppkg2's Avatar
ppkg2 ppkg2 is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
28-Jun-2012, 10:43 AM #3
Dear Valis,
I have excel sheet with 3000 trademark application numbers. I need to check the online status every week and have to send emails to clients. Till now I am doing manually. I need the STATUS date from result page on entering in Trademark Application query box. Guide me pls.
ppkg2's Avatar
ppkg2 ppkg2 is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
28-Jun-2012, 12:56 PM #4
Please ...anybody is there to solve my issue
ppkg2's Avatar
ppkg2 ppkg2 is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
28-Jun-2012, 02:02 PM #5
is it really difficult?
spatha's Avatar
spatha spatha is offline
Computer Specs
Member with 82 posts.
 
Join Date: Jun 2011
29-Jun-2012, 12:34 PM #6
Is the excel sheet just a single column of the application numbers?
spatha's Avatar
spatha spatha is offline
Computer Specs
Member with 82 posts.
 
Join Date: Jun 2011
29-Jun-2012, 03:29 PM #7
Here's what I came up with. You'll have to allow ActiveX controls and it assumes that you just have two columns. A:Number and B:Status. The macro is called Pull_Status.
Attached Files
File Type: xlsm StatusPull.xlsm (26.4 KB, 102 views)
ppkg2's Avatar
ppkg2 ppkg2 is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
29-Jun-2012, 05:10 PM #8
Dear Spatha,
Thank you so much....I copied the column to your sheet....its working....then i am copying back the results to my original sheet....thank u so much
ppkg2's Avatar
ppkg2 ppkg2 is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
30-Jun-2012, 01:10 AM #9
Hi..while running the macro error popup "Object vaiable or With block variable not set"

How to overcome this?
Attached Thumbnails
Solved: How to pull data from webpage(.aspx) for a excel column?-error1.jpg  
ppkg2's Avatar
ppkg2 ppkg2 is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
02-Jul-2012, 12:45 AM #10
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 <> "<FONT color=red><B>(NOT FOR LEGAL USE)</B></FONT>"

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, "&nbsp;", 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
ppkg2's Avatar
ppkg2 ppkg2 is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
02-Jul-2012, 12:45 AM #11
while running this macro error popup "Object vaiable or With block variable not set"

pls find out where is the error.....
ppkg2's Avatar
ppkg2 ppkg2 is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
02-Jul-2012, 01:23 PM #12
can u chk it
spatha's Avatar
spatha spatha is offline
Computer Specs
Member with 82 posts.
 
Join Date: Jun 2011
02-Jul-2012, 05:58 PM #13
I honestly don't know. It runs perfectly for me. I even inputed the numbers you have in your screenshot, they all worked fine.

Does it give you the option to debug when you get the error message? If so, what line does it highlight?
ppkg2's Avatar
ppkg2 ppkg2 is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
02-Jul-2012, 09:18 PM #14
no..it is not giving any debug option....just this error popping up...and i am re running the macro.....same popup repeats in few secs...
spatha's Avatar
spatha spatha is offline
Computer Specs
Member with 82 posts.
 
Join Date: Jun 2011
03-Jul-2012, 11:38 AM #15
I was able to recreate your error, though not consistently. I made a few changes to the code and added some more error handling. I ran the macro 5 or 6 times to be sure, and I didn't get any errors.
Attached Files
File Type: xlsm StatusPull.xlsm (27.9 KB, 82 views)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑