Querying the web from a list of links?

[tmcmahon]

I am trying to compile some data from a series restaurant inspections posted on the web.

Each inspection has its own URL. The URLs are identical, except that each ends in a different 5-digit number.

I have a list of all the 5-digit numbers in a spreadsheet (about 3000+).

Is there any way to run a macro that will query each website in sucession, each time replacing the 5-digit number in the URL? I'd like to import the data into a separate worksheet.

Thanks for any help!

 

[sweater_vests_rock]

tmcmahon.

are you always importing the same table? are these webpages as identical as their urls? if so, then yes, you could loop a web query through. if not, then i'm not sure it is possible.

ben.

 

[tmcmahon]

I agree, Sweater vests do rock...

Yes the web sites all look basically the same.

Here is an example: http://www.region.waterloo.on.ca/web/foodinspection.nsf/aaShowDetails?openagent&pid=05210

Sometimes there is a table (As in the link I've pasted here). However, when there is no information, there is no table. There's just a note saying "No information available."

The tables are of varying lengths, depending on how many times the restaurant has been inspected.

 

[sweater_vests_rock]

tmc.

try the below code on a SMALL test case -- i picked 5210 and 5211, but i would recommend picking one with no information to see if the code errors out.

i wouldn't advise using this code on your entire sample yet, as i think this would take a prohibitively long amount of time to complete. i think the trick to using this code would be to find the command which would allow you to set up a web query but not refresh the data. you could then load your web queries all at once, which might be faster.

the other watchout is that your excel file will probably become prohibitively large with this number of web queries. i'm not even sure if a workbook can hold 3000 worksheets.

these problems aren't insurmountable, but i wanted to let you know that i've not tried to address them with this coding.

hope this moves you in the right direction.
ben.

Sub MultipleWebQueries()

    Dim MyArray(), MyRange As Range
    Dim StartRow As Long, StartColumn As Integer
    
    StartRow = 1 '<-- Change this to the first row # of your list
    StartColumn = 1 '<-- Change this to the column # of your list
    
    Set MyRange = Range(Cells(StartRow, StartColumn), Cells(Rows.Count, StartColumn).End(xlUp))
    MyArray = MyRange
    
    Application.ScreenUpdating = False
    
    For i = LBound(MyArray, 1) To UBound(MyArray, 1)
        ActiveWorkbook.Worksheets.Add.Name = MyArray(i, 1)
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://www.region.waterloo.on.ca/web/foodinspection.nsf/aaShowDetails?openagent&pid=0" & MyArray(i, 1) _
            , Destination:=Range("A1"))
            .Name = "aaShowDetails?openagent&pid=" & MyArray(i, 1)
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "2"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        
        Application.ScreenUpdating = True
        
    Next i
End Sub

 

[tmcmahon]

Thanks for this. I'll test it out.

I don't need each individual inspection record on a new worksheet, I would just like the results of the query to appear on a different worksheet than my list of URLs.

Basically, my list of URLS is in worksheet 1 and I'd like all the results to be in worksheet 2.

If there is a way to tweak this code that you've written here so that the results don't each appear on their own worksheet, let me know.

Thanks a lot for this!

 

[tmcmahon]

HI,

It worked, but is there any way to change it so that the results all appear on the same worksheet?

Cheers.

 

[sweater_vests_rock]

tc.

i think this should do the trick for you. glad everything worked as expected!

cheers. ben.

Sub MultipleWebQueries()

    Dim MyArray(), MyRange As Range
    Dim MyDestination as Range
    Dim StartRow As Long, StartColumn As Integer
    Dim ws as Worksheet, i as Integer
    
    StartRow = 1 '<-- Change this to the first row # of your list
    StartColumn = 1 '<-- Change this to the column # of your list
    
    Set MyRange = Range(Cells(StartRow, StartColumn), Cells(Rows.Count, StartColumn).End(xlUp))
    MyArray = MyRange
    
    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        If Left(ws.Name, Len("Web Queries")) = "Web Queries" Then i = i + 1
    Next ws
    
    If i <> 0 Then
        ActiveWorkbook.Worksheets.Add.Name = "Web Queries (" & i & ")"
    Else
        ActiveWorkbook.Worksheets.Add.Name = "Web Queries"
    End If
    
    
    For i = LBound(MyArray, 1) To UBound(MyArray, 1)
        
        Set MyDestination = ActiveSheet.UsedRange.Offset(2, 0)
        
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://www.region.waterloo.on.ca/web/foodinspection.nsf/aaShowDetails?openagent&pid=0" & MyArray(i, 1) _
                , Destination:=MyDestination)
            .Name = "aaShowDetails?openagent&pid=" & MyArray(i, 1)
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "2"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
                
    Next i

    Application.ScreenUpdating = True

End Sub

 

[FerVerX]

Thanx from me too, couse I have similar problem!

The thing for me is that I too have sheet with numbers I need to put into URL, but I also need to make first column to have the "05000" or other "endings of URL" value, How to?
And I have also one more variable changing on my link: http://market.rfb.lv/index.php?pg=d...=4&popup=trades&downloadcsv=1&date=2005-11-23 and that is "LV0000100626" which of I have list on sheet (A1:A2 - LV0000101103, and B1:B2 - GZE1R and LSC1R - I need that sheet name for number A2 the representative cell B2 (LSC!R)) any ideas?