Web Query Returns Wrong Tables

[basic09]

Hello everyone! I'd like to thank anybody in advance with any advice on this particular issue.

I'm trying to extract certain tables in Yahoo! Finance, however when these tables are refreshed in Excel 2007, the web query will return tables that I did not select initially.

For example if I took the ticker symbol INFA (Informatica Corp.) and looked at the Analyst Estimate page (http://finance.yahoo.com/q/ae?s=INFA). I select the Earnings Est. and Revenue Est. tables in the web query. After loading them into Excel, I'll refresh them and suddenly the web query gives me the Earnings History table on the page! Or the table will look completely mangled in Excel because it tried to load some other table (the rows shift and it looks quite messy).

I did some research and found that Excel can't import dynamically scripted tables (http://support.microsoft.com/kb/277899)... and after numerous tries, I can only conclude that this must be the problem. Can anyone enlighten me on my dilemma? Or am I missing something completely?

Thanks!
Dave

 

[Legacy 98055]

Hi Dave. Here is a workaround using IE to download the source to file and then query the file that is on disk. See the example that is ready to roll...

<a href="http://home.fuse.net/tstom/0702081848.328072.zip"><img src="http://home.fuse.net/tstom/zip.gif"width="48"height="48"border="0"></a> <a href="http://home.fuse.net/tstom/0702081848.328072.zip">0702081848.328072.zip</a>

Add UserForm1 containing WebBrowser1.
Placement does not matter as you will never see the form.
There is no code in UserForm1.

In a standard or public class module:

Private WithEvents wb As WebBrowser
Private uf As New UserForm1
Private URL As String

Sub GetSomeData()
    [E:I].ClearContents
    Set wb = uf.WebBrowser1
    wb.RegisterAsBrowser = True
    URL = "http://finance.yahoo.com/q/ae?s=" & [a1]
    wb.Navigate URL
End Sub

Private Sub wb_DocumentComplete(ByVal pDisp As Object, URL As Variant)
    Dim TempFileName As String
    
    TempFileName = ThisWorkbook.Path & Application.PathSeparator & Timer & ".html"
    Open TempFileName For Output As #1
        Print #1, wb.Document.All(0).outerHTML
    Close #1
    
    With ActiveSheet.QueryTables.Add("URL;file:///" & TempFileName, Range("E1"))
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """yfncsubtit"",14,18"
        .Refresh BackgroundQuery:=False
        .Delete
    End With
    
    With Range("F1:I1")
        .MergeCells = True
        .EntireColumn.AutoFit
    End With
    
    On Error Resume Next
    Kill TempFileName
    Set wb = Nothing
    Unload uf
    Set uf = Nothing
End Sub

 

[basic09]

Much Appreciated! Thanks Tom.