Problem with html tags still displayed in web query

[scarlett3]

Hi

I'm trying to import into Excel from the following url - http://www.europeantour.com/europeantour/season=2010/tournamentid=2010005/stats/roundno=1/index.html - but rather than the data appearing in one column as in the browser, it appears in one cells with the html tags displayed.

Is there a way to get this data to be displayed in Excel in one column and without the tags?

Thanks

 

[scarlett3]

anyone?

 

[John_w]

It's because the HTML is missing table tags so that it can be inserted into another page. Try this:

Sub Golf()

    Dim URL As String
    Dim IE As Object
    Dim elements As Object
    Dim lines As Variant
    
    URL = "http://www.europeantour.com/europeantour/season=2010/tournamentid=2010005/stats/roundno=1/index.html"
    
    Set IE = CreateObject("InternetExplorer.Application")
    
    With IE
        .Visible = True
        .navigate URL
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        Set elements = .document.getElementsByTagName("HTML")
        .Quit
    End With
    
    lines = Split(elements(0).innerText, vbCrLf)
    
    With Worksheets("Sheet1")
        .Columns("A").ClearContents
        .Columns("A").NumberFormat = "@"        'Text format
        .Range("A1").Resize(UBound(lines) - LBound(lines) + 1, 1) = Application.WorksheetFunction.Transpose(lines)
    End With
        
End Sub

 

[scarlett3]

Thanks for the code, john_w.

I'm not sure what to do with it, though. When I run this code, it opens the page that I want to web query in my browser, but does nothing in the Excel file. When I close the browser, I get: run-time error '-2147467259 (80004005)' Automation error Unspecified error.

 

[John_w]

Try this version instead - in converting it to late binding I left a bug in. The code can go in a sheet module or a standard code module, it doesn't matter. Run the Golf macro and it should populate column A on Sheet1.

Sub Golf()

    Dim URL As String
    Dim IE As Object
    Dim elements As Object
    Dim lines As Variant
    
    URL = "http://www.europeantour.com/europeantour/season=2010/tournamentid=2010005/stats/roundno=1/index.html"
    
    Set IE = CreateObject("InternetExplorer.Application")
    
    With IE
        .Visible = True
        .navigate URL
        While .Busy Or .readyState <> 4: DoEvents: Wend
        Set elements = .document.getElementsByTagName("HTML")
        lines = Split(elements(0).innerText, vbCrLf)
        .Quit
    End With
    
    With Worksheets("Sheet1")
        .Columns("A").ClearContents             'Clear column A
        .Columns("A").NumberFormat = "@"        'Text format
        .Range("A1").Resize(UBound(lines) - LBound(lines) + 1, 1) = Application.WorksheetFunction.Transpose(lines)
    End With
        
End Sub

 

[scarlett3]

It worked

Thanks a lot!