VBA scrapping of USPTO website

[ScooterNorm]

Excel Version (Office 2016)

Excel Environment (desktop, Windows)

Knowledge Level - Intermediate level

I'm trying to scrape off the USPTO website. When I do a manual search the URL is: https://tsdr.uspto.gov and I enter a serial number (i.e. 79349658), press the status button and the search is executed and the results are returned.

The resulting URL of the search page is:

https://tsdr.uspto.gov/#caseNumber=...TION&caseType=DEFAULT&searchType=statusSearch

When I try to execute this with VBA, it returns the tsdr.uspto.gov web page, not the result of the search. Any idea what I'm doing wrong?

Here's the code,

Sub Test()
Dim ht As HTMLDocument
Dim IE As InternetExplorer

Set IE = New InternetExplorer
IE.Visible = True
IE.navigate ("Trademark Status & Document Retrieval" & _
"&caseSearchType=US_APPLICATION" & _
"&caseType=DEFAULT" & _
"&searchType=statusSearch")

Do Until IE.readyState = READYSTATE_COMPLETE And IE.Busy = False
DoEvents
Loop

Set ht = IE.document

'IE.Quit

End Sub

 

[Domenic]

Try...

IE.navigate "https://tsdr.uspto.gov/" & _
    "#caseNumber=79349658" & _
    "&caseSearchType=US_APPLICATION" & _
    "&caseType=DEFAULT&searchType=statusSearch"

Hope this helps!

 

[ScooterNorm]

Thanks Dominic,
Yes, that's what I tried. It returns the Search screen as if you entered tsdr.uspto.gov
My post is messed up and I can't figure out how to edit my post.

 

[Domenic]

Actually, in your code, you didn't include the case number, as I did in mine...

 

[ScooterNorm]

Actually, in your code, you didn't include the case number, as I did in mine...

actually I did include the serialno. That’s one of the things messed up on post which I can’t figure out how to fix. But my VBA code has the serialno.

 

[Domenic]

When I try my code, it returns the status result.

If you don't see an "Edit" option/button, you don't have the option to edit your post. In that case, try posting the navigate portion again, but this time make sure that you post them between code tags. You should see a button that looks like this </> on the toolbar above the message area.

 

[ScooterNorm]

When I try my code, it returns the status result.

If you don't see an "Edit" option/button, you don't have the option to edit your post. In that case, try posting the navigate portion again, but this time make sure that you post them between code tags. You should see a button that looks like this </> on the toolbar above the message area.

Humm when I tried it, it returned the tsdr.uspto.gov page, not the search page result.
Thanks for the tip on using the </> I'll try it here again and post my code:

Sub Test()
    Dim IE As InternetExplorer

    Set IE = New InternetExplorer

    IE.Visible = True

    IE.navigate ("https://tsdr.uspto.gov/#caseNumber=79349658" &_
                 "&caseSearchType=US_APPLICATION" & _
                 "&caseType=DEFAULT" & _
                 "&searchType=statusSearch")

    Do Until IE.readyState = READYSTATE_COMPLETE And IE.Busy = False
        DoEvents
    Loop

End Sub

Is that the code you tried?
Also, I'm using Excel Version (Office 2016) on Excel Environment (desktop, Windows).

Thanks again for your suggestions,

 

[ScooterNorm]

Excel Version (Office 2016)

Excel Environment (desktop, Windows)

Knowledge Level - Intermediate level

I'm trying to scrape off the USPTO website. When I do a manual search the URL is: https://tsdr.uspto.gov and I enter a serial number (i.e. 79349658), press the status button and the search is executed and the results are returned.

The resulting URL of the search page is:

Trademark Status & Document Retrieval

When I try to execute this with VBA, it returns the tsdr.uspto.gov web page, not the result of the search. Any idea what I'm doing wrong?

Here's the code,

Sub Test()
Dim ht As HTMLDocument
Dim IE As InternetExplorer

Set IE = New InternetExplorer
IE.Visible = True
IE.navigate ("Trademark Status & Document Retrieval" & _
"&caseSearchType=US_APPLICATION" & _
"&caseType=DEFAULT" & _
"&searchType=statusSearch")

Do Until IE.readyState = READYSTATE_COMPLETE And IE.Busy = False
DoEvents
Loop

Set ht = IE.document

'IE.Quit

End Sub

I’d like to add another point. When I to execute the code with VBA

IE.navigate ("https://tsdr.uspto.gov/" & _
            "#caseNumber=79349658" & _
            "&caseSearchType=US_APPLICATION" & _
            "&caseType=DEFAULT" & _
            "&searchType=statusSearch")

The case number shows up in the search box and in addition a small x appears on the right hand side of the case number when I navigate to the case number data entry. The same x does not show up when I manually navigate to the page.

 

[Domenic]

The 'x' that appears on the right hand side of the case number is there to allow the user to delete the case number by simply clicking on the 'x'. And whenI try your code, this is what I get...

​

 

[ScooterNorm]

The 'x' that appears on the right hand side of the case number is there to allow the user to delete the case number by simply clicking on the 'x'. And whenI try your code, this is what I get...

View attachment 107487

​

But did you do this by loading up IE manually or with the VB script I attached?