Retrieving date but WebQuery not detecting table

[ibTops]

Hi, Im somewhat used to dealing with Excel and Macros, not so much with retrieving data using WebQuerys. Ive been trying to find a solution to this all over the place, but IÂ’ve had no success so far, so I would greatly appreciate any help on the matter

Basically, I have a table of 10000 trademark numbers, and itd be extremely useful if I could obtain a certain date out of a webpage, regarding the priority date of each of them (defined as “Fecha presentacion solicitud otorgada” in the website).

Each trademark has its own particular website, definide as:

http://www.oepm.es/es/signos_distintivos/resultados_expediente.html?mod=M&exp=trademarknumber&bis=

So for example, trademark number 2359879, will have the following unique site:

http://www.oepm.es/es/signos_distintivos/resultados_expediente.html?mod=M&exp=2359879&bis=

Using my list of trademark numbers in Excel and the function CONCATENATE, Ive made a column right next to it, that contains all the links to the corresponding website of each and everyone of the trademarks.

IÂ’ve tried recording a Macro, but the WebQuery doesnÂ’t detect the table and I donÂ’t know how to obtain that particular piece of data (date next to Fecha presentacion solicitud otorgada).

Thanks in advance for any help or input on the matter.

 

[John_w]

Welcome to MrExcel forums.

Web queries won't work because the webpage doesn't use HTML tables.

Instead, request the pages using XMLhttp and extract the date. This code expects the URLs to be in the first sheet in column B starting at B2 and it puts the dates in column C.

Public Sub Get_All_Dates()

    Dim URLcell As Range, URLcells As Range
    
    With Worksheets(1)
        Set URLcells = .Range("B2", .Cells(Rows.Count, "B").End(xlUp))
    End With
    
    For Each URLcell In URLcells
        URLcell.Offset(, 1).Clear
        URLcell.Offset(, 1).Value = Get_Trademark_Date(URLcell.Value)
    Next
    
End Sub


Public Function Get_Trademark_Date(URL As String) As Variant

    Static XMLreq As Object
    Static HTMLdoc As Object
    Dim dateElement As HTMLGenericElement
    Dim i As Long
    
    If XMLreq Is Nothing Then Set XMLreq = CreateObject("MSXML2.XMLHTTP")
    If HTMLdoc Is Nothing Then Set HTMLdoc = CreateObject("HTMLfile")

    With XMLreq
       .Open "GET", URL, False
       .send
       HTMLdoc.Open
       HTMLdoc.write .responseText
       HTMLdoc.Close
    End With
        
    'Find this div element:
    
    '< div class="div2col" >
    '    < h4 class="noWidth" >
    '        Fecha presentación solicitud otorgada:
    '    < /h4 >
    '    < p class="noWidth" >
    '        23/11/2000
    '    < /p >
    '< /div >
    
    Set dateElement = Nothing
    i = 0
    While i < HTMLdoc.all.Length And dateElement Is Nothing
        If InStr(1, HTMLdoc.all(i).innerText, "Fecha presentación solicitud otorgada:") = 1 Then Set dateElement = HTMLdoc.all(i)
        i = i + 1
    Wend
    
    If Not dateElement Is Nothing Then
        Get_Trademark_Date = CDate(Split(dateElement.innerText, vbCrLf)(1))
    Else
        Get_Trademark_Date = "Not found"
    End If
    
End Function

 

[ibTops]

Thanks for the help John_w. I imagined it had something to do with the way data was presented on those websites. I'm having the following issue though when running the macro, relating to: [FONT=&quot]User-defined type not defined

[/FONT]Regarding: Dim dateElement As HTMLGenericElement

[FONT=&quot]
[/FONT]

 

[John_w]

Just change that line to:

Dim dateElement As Object

(or add a reference to MS HTML Object Library via Tools > References.)

 

[ibTops]

It worked!! Can't thank you enough John!! Amazing!! It took a couple hours to get all the data, but it worked great! Thanks again!