Web Query Returns Wrong Tables

basic09

New Member
Joined
Jul 2, 2008
Messages
2
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
L

Legacy 98055

Guest
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:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top