Thread: Retrieving Web Data using Excel

I have kind of a working example. Basically I just pulled the webpage into Excel and then dealt with the information from there.

This is what I came up with. You'll have to be the judge of whether it's of any use to you!!!

Code:

Sub webpage()
    Dim cur_row As Integer
    Dim cur_col As Integer
    cur_col = 1
    cur_row = 0
    'opens the webpage
    Workbooks.Open Filename:= _
        "http://www.calculateme.com/car-insur...ey/absecon.htm"
        Rows("1:10").Delete 'delete the first 10 rows as they don't contain any data we want
        ActiveWorkbook.Worksheets.Add 'add our data sheet. Sheet is added at the front of the workbook, making it sheet(1) and the webpage sheet(2)
        For Each rw In ActiveWorkbook.Worksheets(2).Rows ' search in each row of the webpage sheet
        If Not IsEmpty(ActiveWorkbook.Worksheets(2).Cells(rw.Row, 1)) Then ' filter out any empty cells
        If (ActiveWorkbook.Worksheets(2).Cells(rw.Row, 1).Font.Bold = True) Then 'check if the text is bold (ie a heading)
        
        cur_col = 1 ' reset the column to 1
        cur_row = cur_row + 1 ' add 1 to the row
        ActiveWorkbook.Worksheets(1).Cells(cur_row, cur_col).Value = ActiveWorkbook.Worksheets(2).Cells(rw.Row, 1).Value ' set the value of the first column to the name from the webpage sheet
        ActiveWorkbook.Worksheets(1).Cells(cur_row, cur_col + 4).Value = ActiveWorkbook.Worksheets(2).Cells(rw.Row - 2, 1).Value ' get the distance value 2 lines above and put it at the end
        
        Else ' text is not bold
        
        If InStr(ActiveWorkbook.Worksheets(2).Cells(rw.Row, 1).Value, "distant") = 0 Then ' filter out the distance cells as we're writing these above
        cur_col = cur_col + 1 ' add 1 to the current column
        If cur_row = 0 Then cur_row = 1 ' originally set row to 0 if we were able to remove all rows that didn't contain data
        ActiveWorkbook.Worksheets(1).Cells(cur_row, cur_col).Value = ActiveWorkbook.Worksheets(2).Cells(rw.Row, 1).Value ' set the value of the current cell (cur_row,cur_col) to the value from the webpage row we're reading
        End If ' end of distance filter
        End If ' end of bold filter
        End If ' end of empty filter
        
        Next ' move to next row of webpage
        
End Sub

Obviously, that's just an example to deal with that specific type of page, but I guess you could adapt it to deal with different scenarios. The main point is to find something unique about the header of each block of data and deal with each row accordingly.

Hope that helps.