[ppkg2]

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.

[valis]

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]

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]

Please ...anybody is there to solve my issue

[ppkg2]

is it really difficult?

[spatha]

Is the excel sheet just a single column of the application numbers?

[spatha]

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.

[ppkg2]

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]

Hi..while running the macro error popup "Object vaiable or With block variable not set"

How to overcome this?

[ppkg2]

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]

while running this macro error popup "Object vaiable or With block variable not set"

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

[ppkg2]

can u chk it

[spatha]

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]

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]

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.