Pull data from web within date range or recent date

[Tlandress]

Good day everyone. I have been using a macro to pull data regarding 3 seperate forms from a website table, and was needing help finding a way to find the document with the most recent date. Here is what the table looks like. on the website.

http://www.pangaeadata.com/OCC_Files...api=3511922067

Typically the order the forms go in are 1000,1001A,1002A. So a 1000 form is filed, and then a person will file a 1001A, and once completed they turn in a 1002A. This might happen for the same well numerous times. By the way this info pertains to oil and gas wells. The reason you have more than one is that someone might go back to an old well and recomplete it to see if they can pull more fluid/gas out of the ground. So when this happens a new cycle of forms starts. I would like to figure out how to only find the most recent document for each well in my list of urls like the one above. There are other forms on the website, but i am only concerned with the most recent one. Is there a way I would be able to do this easily? I set up my macro to search heirarchily, but i realized that the most recent document of either a 1000,1001A,or 1002A is all I really need. Any help would be much appreciated. Let me know if this makes sense to anyone or if you need more info from me. I posted the code I am using already below.

Function GetWellData(url As String) As Variant

    Dim arr(1 To 3)
    Dim Doc As Object
    Dim Href As String
    Dim oTable As Object
    Dim PageSource As String
    Dim sLookup As String
    Dim x As Long
        
       
        With CreateObject("msxml2.xmlhttp")
            .Open "GET", url, False
            .send
            PageSource = .responseText
        End With
        
          ' Create the HTML Document object.
            Set Doc = CreateObject("htmlfile")
            Doc.Open url:="text/html", Replace:=True
            Doc.write PageSource
        
            Set oTable = Doc.getElementById("DataGrid")
                
            For x = 2 To oTable.Rows.Length - 1
                If oTable.Rows(x).Cells(0).ChildNodes(0).nodeName = "A" Then
                    Href = oTable.Rows(x).Cells(0).ChildNodes(0).Href
                
                    sLookup = oTable.Rows(x).Cells(1).innerText
                    Select Case sLookup
                    Case Is = "1000"
                        If Val(sLookup) > Val(arr(1)) Then
                            arr(1) = sLookup
                            arr(2) = oTable.Rows(x).Cells(6).innerText
                        End If
                    Case Is = "1001A"
                        If Val(sLookup) > Val(arr(1)) Then
                            arr(1) = sLookup
                            arr(2) = oTable.Rows(x).Cells(6).innerText
                        End If
                    Case Is = "1002A"
                        If Val(sLookup) > Val(arr(1)) Then
                            arr(1) = sLookup
                            arr(2) = oTable.Rows(x).Cells(6).innerText
                            arr(3) = Href
                        End If
                    End Select
                End If
            Next x
    
      GetWellData = arr
      
End Function

[Tlandress]

Hope someone can help out. Last thing I need to get this to work exactly how I want. Any help is much appreciated.

[Tlandress]

*bump* Could someone help to point me in the right direction?

[Tlandress]

*bump up the jam bump it up*

[Kyle123]

Assuming test date:

Function GetWellData(url As String) As Variant

    Dim arr(1 To 3)
    Dim Doc As Object
    Dim oTable As Object
    Dim PageSource As String
    Dim baseDate As Date
    Dim x As Long
        
       
        With CreateObject("msxml2.xmlhttp")
            .Open "GET", url, False
            .send
            PageSource = .responseText
        End With
        
          ' Create the HTML Document object.
            Set Doc = CreateObject("htmlfile")
            Doc.body.innerhtml = PageSource
        
            Set oTable = Doc.getElementById("DataGrid")
            
            baseDate = CDate(oTable.Rows(2).Cells(6).innerText)
            
            For x = 2 To oTable.Rows.Length - 1
            
                If oTable.Rows(x).Cells(0).ChildNodes(0).nodeName = "A" Then
                    If CDate(oTable.Rows(x).Cells(6).innerText) > baseDate Then
                        arr(1) = oTable.Rows(x).Cells(1).innerText
                        arr(2) = oTable.Rows(x).Cells(6).innerText
                        arr(3) = oTable.Rows(x).Cells(0).ChildNodes(0).Href
                        baseDate = CDate(oTable.Rows(x).Cells(6).innerText)
                    End If
                End If
                
            Next x
    
      GetWellData = arr
      
End Function

[Tlandress]

Thanks for replying. I just ran it and it seems to work fine, but if I wanted to only find the recent dates for the 1000, 1001A, and 1002A forms would that be fairly easy or pretty difficult. My boss kinda goes back and forth with what he wants so if I needed to change it to find the specific forms would I be able to incorporate the recent date script with the old script that searches for only the 1000, 1001A, 1002A forms?

[Kyle123]

Is it the scan date or test date you're after? This is actually easier than the original query, since you aren't concerned about name, simply the date

[Tlandress]

Thanks for replying. I just ran it and it seems to work fine, but if I wanted to only find the recent dates for the 1000, 1001A, and 1002A forms would that be fairly easy or pretty difficult. My boss kinda goes back and forth with what he wants so if I needed to change it to find the specific forms would I be able to incorporate the recent date script with the old script that searches for only the 1000, 1001A, 1002A forms?

[Tlandress]

Just realized why you thought I was just looking for only the latest date of any file. I kinda mixed up my wording on what I needed. I was needing the latest testdate of only the 1000/1001A/1002A's. I can probably intergrate what you have done with the old code, but I won't be able to play around with it for a couple days. Ill get back to you with what I accomplish. Thanks again for the help. Ill leave this thread open til I actually get to testing out some stuff.

[Tlandress]

Just realized why you thought I was just looking for only the latest date of any file. I kinda mixed up my wording on what I needed. I was needing the latest testdate of only the 1000/1001A/1002A's. I can probably intergrate what you have done with the old code, but I won't be able to play around with it for a couple days. Ill get back to you with what I accomplish. Thanks again for the help. Ill leave this thread open til I actually get to testing out some stuff.

[Tlandress]

Would it be easiest to add the date into the three slookup areas from the previous code with the If CDate(oTable.Rows(x).Cells(6).innerText) > baseDate code? It seems simple enough but when I do that it doesnt reallly do anything. Just stops responding and crashes Excel. Kinda weird.

[Tlandress]

Yeah. So i guess I dont know as much as I wish I did. Been messing with it for a few hours trying to merge the two together with not so much luck.

Here is what i have so far:

Function GetWellData(url As String) As Variant

    Dim arr(1 To 3)
    Dim Doc As Object
    Dim Href As String
    Dim oTable As Object
    Dim PageSource As String
    Dim sLookup As String
    Dim baseDate As Date
    Dim x As Long
        
       
        With CreateObject("msxml2.xmlhttp")
            .Open "GET", url, False
            .send
            PageSource = .responseText
        End With
        
          ' Create the HTML Document object.
            Set Doc = CreateObject("htmlfile")
            Doc.Open url:="text/html", Replace:=True
            Doc.body.innerhtml = PageSource
        
            Set oTable = Doc.getElementById("DataGrid")
            
            baseDate = CDate(oTable.Rows(2).Cells(6).innerText)
                
            For x = 2 To oTable.Rows.Length - 1
            
                If oTable.Rows(x).Cells(0).ChildNodes(0).nodeName = "A" Then
                    If Href = oTable.Rows(x).Cells(0).ChildNodes(0).Href Then
                        sLookup = oTable.Rows(x).Cells(1).innerText
                            If CDate(oTable.Rows(x).Cells(6).innerText) > baseDate Then
                    Select Case sLookup
                    Case Is = "1000"
                        If Val(sLookup) > Val(arr(1)) Then
                            If CDate(oTable.Rows(x).Cells(6).innerText) > baseDate Then
                                arr(1) = oTable.Rows(x).Cells(1).innerText
                                arr(2) = oTable.Rows(x).Cells(6).innerText
                                baseDate = CDate(oTable.Rows(x).Cells(6).innerText)
                        End If
                    Select Case sLookup
                    Case Is = "1001A"
                        If Val(sLookup) > Val(arr(1)) Then
                            If CDate(oTable.Rows(x).Cells(6).innerText) > baseDate Then
                                arr(1) = oTable.Rows(x).Cells(1).innerText
                                arr(2) = oTable.Rows(x).Cells(6).innerText
                                baseDate = CDate(oTable.Rows(x).Cells(6).innerText)
                        End If
                    Select Case sLookup
                    Case Is = "1002A"
                        If Val(sLookup) > Val(arr(1)) Then
                            If CDate(oTable.Rows(x).Cells(6).innerText) > baseDate Then
                                arr(1) = oTable.Rows(x).Cells(1).innerText
                                arr(2) = oTable.Rows(x).Cells(6).innerText
                                arr(3) = oTable.Rows(x).Cells(0).ChildNodes(0).Href
                                baseDate = CDate(oTable.Rows(x).Cells(6).innerText)
                        End If
                    End Select
                End If
            Next x
    
      GetWellData = arr
      
End Function

Here is the Workbook. PERMITS_TO_COMPLETIONS_AUTOUP2.xlsm


Trying to get it to the latest date in the same hierarchical order as before.

Example: Find 1000 forms>Extract latest Date for 1000 form
>Find 1001A if it exists>Overwrite 1000 date with 1001A latest date
>Find 1002A if it exists>Overwrite 1001A date with 1002A latest date>Extract link to pdf download.

I probably screwed the pooch on the code above. Learned this stuff pretty well 8 years ago, but just started using it again.

****Tried to delete the double posts but cant really find how to delete them. Not sure why its not just a button to delete, but yet again I may just be retarded.*****

[Tlandress]

Should I start a new thread and mark this as solved. It is mostly want I wanted, and I will continue to try it out, but from my original post Kyle123 did actually solve what I initially asked. Let me know. Also, is there a donation link for this forum. Been really helpful in streamlining my workflow. Wanted to show some appreciation.

[Kyle123]

Hi,

I'll have a look at it in the morning for you

[Kyle123]

Try this:

Function GetWellData(url As String) As Variant

    Dim arr(1 To 3)
    Dim Doc As Object
    Dim oTable As Object
    Dim PageSource As String
    Dim baseDate As Date
    Dim x As Long

    Const sForms As String = "|1000|1001A|1002A|"
       
        With CreateObject("msxml2.xmlhttp")
            .Open "GET", url, False
            .send
            PageSource = .responseText
        End With
        
          ' Create the HTML Document object.
            Set Doc = CreateObject("htmlfile")
            Doc.body.innerhtml = PageSource
        
            Set oTable = Doc.getElementById("DataGrid")
            
            baseDate = CDate(oTable.Rows(2).Cells(6).innerText)
            
            For x = 2 To oTable.Rows.Length - 1
                If InStr(1, sForms, "|" & oTable.Rows(x).Cells(1).innerText & "|") Then
                    If oTable.Rows(x).Cells(0).ChildNodes(0).nodeName = "A" Then
                        If CDate(oTable.Rows(x).Cells(6).innerText) > baseDate Then
                            arr(1) = oTable.Rows(x).Cells(1).innerText
                            arr(2) = oTable.Rows(x).Cells(6).innerText
                            arr(3) = oTable.Rows(x).Cells(0).ChildNodes(0).Href
                            baseDate = CDate(oTable.Rows(x).Cells(6).innerText)
                        End If
                    End If
                End If
            Next x
    
      GetWellData = arr
      
End Function

[Tlandress]

Thanks man. Your code runs a whole lost faster than the last one I was using. Works great, but i it will only extract the data if it has more than one date. So if there is only one date for any of the forms it will just display a 0 for each array.

[Kyle123]

have you got a url with only one record?

[Tlandress]

Yeah. About a third of them have a single record for them. Went through to see why I was getting no return, and narrowed it down to it only having one instance of the record/date. So if only a single date is present it wont post. Which it seems to only apply to the 1000 form. Since it will have just the one date if its a completely new record.
Not sure if these are helpful, but here are links:

One record:
http://www.pangaeadata.com/OCC_Files...api=3507124616

More than one:
http://www.pangaeadata.com/OCC_Files...api=3507124618

[Tlandress]

I need to read up on the basics apparently. I felt that I knew more, but I am not able to change your code to get it to work. It seems as though it would be easy to use the old one and incorporate parts of it into your code, but i am failing on getting it to work. Any good websites to give me a good run through of the ins and outs of writing these macros.

[Kyle123]

Not really that I know of - this one's pretty straightforward though. Set a break point in the function and step through with the locals window open and you should be able to follow it.

Function GetWellData(url As String) As Variant

    Dim arr(1 To 3)
    Dim Doc As Object
    Dim oTable As Object
    Dim PageSource As String
    Dim baseDate As Date
    Dim x As Long
    'List all the
    Const sForms As String = "|1000|1001A|1002A|"
        
        'Get the web page
        With CreateObject("msxml2.xmlhttp")
            .Open "GET", url, False
            .send
            PageSource = .responseText
        End With
        
          ' Create the HTML Document object.
            Set Doc = CreateObject("htmlfile")
            Doc.body.innerhtml = PageSource
            'Get the table from the page
            Set oTable = Doc.getElementById("DataGrid")
            
            'Set the base date, a date first occurs on thirs row and 7th column
            baseDate = CDate(oTable.Rows(2).Cells(6).innerText)
            
            'Check if the text from the first row contains any of the terms we are looking for
            If InStr(1, sForms, "|" & oTable.Rows(2).Cells(1).innerText & "|") Then
                'If so populate the array with the data from the relevant cells
                With oTable.Rows(2)
                    arr(1) = .Cells(1).innerText
                    arr(2) = .Cells(6).innerText
                    arr(3) = .Cells(0).ChildNodes(0).href
                End With
            End If
            
            'Loop through the other rows in the table
            For x = 2 To oTable.Rows.Length - 1
                'Check the 2nd column of the row contains one of the terms
                If InStr(1, sForms, "|" & oTable.Rows(x).Cells(1).innerText & "|") Then
                    'Make sure that the first column contains a hyperlink
                    If oTable.Rows(x).Cells(0).ChildNodes(0).nodeName = "A" Then
                        'Check that the date in the row is later than the one we have stored
                        'if so overwrite the values in the array with the values in the row
                        If CDate(oTable.Rows(x).Cells(6).innerText) > baseDate Then
                            arr(1) = oTable.Rows(x).Cells(1).innerText
                            arr(2) = oTable.Rows(x).Cells(6).innerText
                            arr(3) = oTable.Rows(x).Cells(0).ChildNodes(0).href
                            baseDate = CDate(oTable.Rows(x).Cells(6).innerText)
                        End If
                    End If
                End If
            Next x
        
      GetWellData = arr
      
End Function

[Tlandress]

The code works really well, but runs into some problems when there are dates that are the same. I changed
baseDate = CDate(oTable.Rows(2).Cells(6).innerText) to baseDate = 1 - CDate(oTable.Rows(2).Cells(6).innerText) and it seems to work for the ones that have forms with the same date. Only problem I am having right now is with one record that has 3 forms with all the same dates. The above code fixes the ones where there are a couple forms with the same date. If its an easy fix please let me know. If not I can deal with one not showing up properly out of the 1000 that I ran. You're awesome man. Helped me so much with this. Very much appreciated.