1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by ppkg2, Jun 28, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. ppkg2

    ppkg2 Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    12
    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.
     
  2. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    67,558
    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?
     
  3. ppkg2

    ppkg2 Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    12
    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.
     
  4. ppkg2

    ppkg2 Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    12
    Please ...anybody is there to solve my issue
     
  5. ppkg2

    ppkg2 Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    12
    is it really difficult?
     
  6. spatha

    spatha

    Joined:
    Jun 20, 2011
    Messages:
    82
    Is the excel sheet just a single column of the application numbers?
     
  7. spatha

    spatha

    Joined:
    Jun 20, 2011
    Messages:
    82
    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:

  8. ppkg2

    ppkg2 Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    12
    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
     
  9. ppkg2

    ppkg2 Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    12
    Hi..while running the macro error popup "Object vaiable or With block variable not set"

    How to overcome this?
     

    Attached Files:

  10. ppkg2

    ppkg2 Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    12
    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
     
  11. ppkg2

    ppkg2 Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    12
    while running this macro error popup "Object vaiable or With block variable not set"

    pls find out where is the error.....
     
  12. ppkg2

    ppkg2 Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    12
    can u chk it
     
  13. spatha

    spatha

    Joined:
    Jun 20, 2011
    Messages:
    82
    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?
     
  14. ppkg2

    ppkg2 Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    12
    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...
     
  15. spatha

    spatha

    Joined:
    Jun 20, 2011
    Messages:
    82
    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:

  16. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1058880