Getting Data From a web site into Excel

[ercedwards]

I have the following macro which has worked for ages until the web page format changed.

Now, this macro still pulls most of the data but doesn't retrieve the real data I need which is the actual fire runs below the headings.

Can anyone help. You can drop this macro into any blank workbook to see what I mean.

Thanks much.

Sub Download()
    Worksheets("CADScreen").Select
    On Error GoTo CADDown
    Application.ScreenUpdating = False
    Range("A:AZ").Select
    Selection.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;https://www.toronto.ca/community-people/public-safety-alerts/alerts-notifications/toronto-fire-active-incidents/#main", Destination:=Range("$A$1"))
        .Name = "TFSCad"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
         Range("A1").Select
    End With
    GoTo CADOK
CADDown:
    Range("A1") = "CAD is Down"
    GoTo Done
CADOK:
     Range("J1") = ""
    Application.ScreenUpdating = True
Done:
End Sub

 

[Roderick_E]

Looks like the data is no longer in an actual table but wrapped in an iframe or something so your code is grabbing everything outside that wrapper. Go to page with a browser and view source

 

[ercedwards]

Roderick,

I'm a real rookie at this. I looked at the source and it looks like what I need is in a table.
What is really strange is that if I go to the web page, right click and select all then copy and paste into my worksheet, everything is fine.
If I use ExecWB 17 to select all and ExecWB 12 to copy, the data I want isn't copied. I thought ExecWB 17 was supposed to work.