Query Multiple Web Pages> Sort For Specific Criteria > Save Lists of Sort in txt files

Hello everyone,
I am looking for some help in getting a macro to query multiple webpages and then sort that data to see if three file types are within the table. From there I want the file types to be separated into 3 lists that save to 3 txt files.

For example: This is the base url for the site. http://www.pangaeadata.com/OCC_Files...api=3507124329
I want the 10 digits on the end to change according to a list I have with ~1000 different sets of numbers.

The table for the URL looks like this:

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

The ten digit number references an oil well. Which is located at the top of the table. I want to search all my wells and see what forms are available for them in excel.
Basically, I am wanting a query to search for all wells that have a 1000/1001A/1002A form filed and if it has one, I want it to put that API number into a list with others with 1000/1001A/1002A forms filed and save a txt file for each different form filed.
If possible I would like a to have a hierarchy where if a 1002A form has been filed it won't look for the other two files. ex. 1002A>1001A>1000 (if 1002A is available I want that API number saved, if there is no 1002A yet, then I want the 1001A API number saved, and so on.) I understand how to do the web query and the txt file save, but I want it to update with the latest info weekly and automatically give me a list.

Any help is very much appreciated. Im interning right now, so if I can get this to work I will look real good. Plz help.

Bump* any help folks?

Can anyone give me a starting point? Trying to understand how to do this by Monday so I can get the spreadsheet running this coming week.

Bump* really need help folks. Can't figure out how to query a list of websites. Have the save list to files part down, but can't get the macro for sorting to work right and I have resorted to making a web query for each site one by one. Which is not ideal when I am dealing with thousands of sites. Help please!!!'

bump up the jam?

persistence is key *bump*

Thanks for the starting point. I realize now how complicated it is after looking around. I'll give it a run and post my results. Just wasn't sure how easy it was to loop that query from a list. Should I look up how to use a query table in this forum? To answer the question about updating. The form numbers indicates what stage the well is in the process if completion. You first get a permit(1000) and completion is 1002A. These forms can come out weeks/months after each other. I would like an update of each well in the process weekly and with the three separate lists I will plot points on a map to show me where people are and in what stage they are in. I appreciate the help. I'll get back as soon as I can.

*bump* just seein if anyone can help me before i head out today

bump. still stuck on getting the info to sort the way i want it
. please help.

Is it possible that a well won't even have a 1000 form filed? If so what would you want returning?

Could you also post a sample workbook with a few urls in please?

Ok, give this a whirl. I noticed in your screenshots that you have the full url in your workbook, so this codes uses that rather than the well ID

Public Function GetWellData(url As String) As Variant
    Dim arr(1 To 2) As String
    Dim strLookup As String
    Dim oHtm As Object: Set oHtm = CreateObject("htmlfile")

    With CreateObject("msxml2.xmlhttp")
        .Open "GET", url, False
        .send
        oHtm.body.innerhtml = .responsetext
    End With
    With oHtm.getelementsbytagname("table")(0)
        For x = 2 To .Rows.Length - 1
            sLookup = .Rows(x).Cells(1).innerText
            Select Case sLookup
                Case Is = "1000"
                    If Val(sLookup) > Val(arr(1)) Then
                        arr(1) = sLookup
                        arr(2) = .Rows(x).Cells(6).innerText
                    End If
                Case Is = "1001A"
                    If Val(sLookup) > Val(arr(1)) Then
                        arr(1) = sLookup
                        arr(2) = .Rows(x).Cells(6).innerText
                    End If
                Case Is = "1002A"
                    If Val(sLookup) > Val(arr(1)) Then
                        arr(1) = sLookup
                        arr(2) = .Rows(x).Cells(6).innerText
                    End If
                Case Else
            End Select
        Next x
    End With
    GetWellData = arr
End Function

To use this, put it in a standard module, the function returns an array so should be used like this:

Assuming the url is in A1
Select B1 and C1, enter the formula:

PHP Code: 

=GetWellData(A1) 


Hold Control + Shift + Enter instead of just pressing enter

The Latest form will be placed into B1 and the date into C1

You can then copy this formula down

You'll need to hit Control + Alt + F9 to refresh all the formulas whenever you want to update

Thanks for the Rep, did this do what you want?

Still trying to get it to work. I haven't had a good moment to look through it though. Finals week at school so trying to get that done first. Ill try it as soon as possible and let you know.

Just finally got around to testing your code. Worked flawlessly. Thanks again for all the help. One last thing. How would I add the ability to export API# (the number at the end of the web address) into 3 separate text files of the 3 different files. Basically just want to make a list of APIs with 1000 files, 1001A files and the 1002A files separately. I have done it before but not sure how to add it to the code you gave me.

Bump* bump* bump* bump* .........bump*