Web Query with Loop 1

[DrMingle]

Object: to connect to the below URL, loop through number 1 - 4301, and copy the important elements to an active worksheet in Excel.

Below is the recorded macro for #4301. I need help with figuring out to automate the numbering of ".name=" and modify the ending of the "URL". The other item I need help with is bringing down the data where it doesn't overwrite each other...some how change the "Destination:=Range".

Your help is appreciated.

Sub Macro2()
'
' Macro2 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;[URL unfurl="true"]http://www.okmedicalboard.org/physical_therapists/licensee/PT/4301",[/URL] _
        Destination:=Range("$A$1"))
        .Name = "4301_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[SkipVought]

hi,

    Dim lRow As Long, i As Integer
    
    For i = 1 To 4301
        lRow = ActiveSheet.[A1].CurrentRegion.Rows.Count + 1
        
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;[URL unfurl="true"]http://www.okmedicalboard.org/physical_therapists/licensee/PT/"[/URL] & i, _
            Destination:=ActiveSheet.Cells(lRow, 1))
            .Name = "qry" & i
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
    Next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DrMingle]

Thanks Skip...just what I needed.

When I have run similar code in the past I have an experienced an error 6 overstack...almost always on the line:

"For i = 1 To 4301"

NOTE: Your solution works perfectly, so I am not interested in tweaking your solution.

I'm curious if you think the previous issues have to do with the range being to large. For example, instead of "1 To 4301" I used "2300 to 54000" or if it is because I didn't start with one, or if it something with my cache configuration.

Thanks again for the straightforward solution.

 

[SkipVought]

Integer data types can only have max value of 32,767. So if your variable will exceed that data value, it should be declared as a Long data type, which can have a max value to 2,147,483,647.

See VB Help.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]