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]

Here's an alternative method...

Sub test()

    'Set a reference (VBE > Tools > References) to the following libraries:
    '   1) Microsoft XML, v6.0
    '   2) Microsoft HTML Object Library

    Dim xmlReq As MSXML2.ServerXMLHTTP60
    Dim htmlDoc As MSHTML.HTMLDocument
    Dim strURL As String
    Dim strResp As String
    
    Set xmlReq = New MSXML2.ServerXMLHTTP60
    
    Set htmlDoc = New MSHTML.HTMLDocument
    
    strURL = "https://tsdr.uspto.gov/statusview/sn79349658"
    
    With xmlReq
        .Open "GET", strURL, False
        .send
        If .Status <> 200 Then
            MsgBox "Error " & .Status & ":  " & .statusText
            Exit Sub
        End If
        strResp = .responseText
    End With
    
    htmlDoc.body.innerHTML = strResp
    
    'etc
    '
    '
    
    Set xmlReq = Nothing
    Set htmlDoc = Nothing

End Sub

Hope this helps!

 

[Domenic]

Yep, seems fine to me. When I run the following, it runs successfully, without and errors...

    Set elems1 = htmlDoc.getElementsByClassName("double table")
    
    For i = 0 To elems1.Length - 1
            Set elems2 = elems1(i).getElementsByClassName("value")
            Debug.Print elems2(0).innerText
    Next i

By the way, with regards to your declaration...

Dim elems1, elems2, elems3 As Object

...only elems3 is declared as Object, the others are declared as Variant. To declare all as Object...

Dim elems1 as Object, elems2 as Object, elems3 As Object

 

[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.