web query seems to run, but no data is displayed

[richardcarter]

I am trying to import a table from a website just simply using the 'Data from web' function in Excel 2010 which seems to run fine but no data is actually shown in the worksheet even after the 'getting data......' has finished running.

The website I am looking at is: http://mis.tse.com.tw/stock_category.html# (you may need to press the 'English' button to show the page in English.. )... and then select the 'Plastic industry' from the pulldown menu to show the table

I am guessing that either the website in question is restricting web queries (is that even possible?) or that I need to somehow identify the direct URL of the table in question as it is using javascipt... Can anyone confirm which of these cases it might be (or anything else)?

 

[p45cal]

I'm no expert in doing web queries, but a bit of googling and experimenting got me the following. I fancy there'll be better ways to do it without invoking an instance of IE and I'll keep looking, in the meantime (adapted from Norie's solution here). See comments in code too re English language:

Sub GetSomeData()
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://mis.tse.com.tw/stock_category.html#"
        Do Until .ReadyState = 4: DoEvents: Loop
'Enable the next commented-out lines to get english version (though sometimes the code moves on before the page has finished updating)
'        Set myButton = .document.all.Item("langEng")
'        myButton.Click
'        Do Until .ReadyState = 4: DoEvents: Loop
'        Do While .Busy: DoEvents: Loop
        Set doc = IE.document
        GetOneTable doc, 1
        .Quit
    End With
End Sub
Sub GetOneTable(d, n)
' d is the document
' n is the table to extract
Dim e As Object ' the elements of the document
Dim t As Object ' the table required
Dim r As Object ' the rows of the table
Dim c As Object ' the cells of the rows.
Dim I As Long
Dim J As Long

DoEvents '(seemed to solve English version problem!)
    For Each e In d.all
        If e.nodename = "TABLE" Then
            J = J + 1
        End If
        If J = n Then
            Set t = e
            tabno = tabno + 1
            nextrow = nextrow + 1
            Set rng = Range("A" & nextrow)
            For Each r In t.Rows
                For Each c In r.Cells
                    rng.Value = c.innertext
                    Set rng = rng.Offset(, 1)
                    I = I + 1
                Next c
                nextrow = nextrow + 1
                Set rng = rng.Offset(1, -I)
                I = 0
            Next r
            Exit For
        End If
    Next e
End Sub

 

[richardcarter]

Seems my original reply didn't get posted.. so again:

Wow! thats fantastic... the code works well, thank you! You have provided a lot of code in there, which on the surface, I think I can apply to other similar queries... once I have managed to decipher it all as I think I can apply it to other similar web queries that I have been trying to put together. The 'invoking IE' part also captured my attention which I will look into more.

After pulling my hair out over this, I also managed to find another solution by using Firefox web developer mode which revealed the underlying source url for each line of data (basically each line had a csv file) and I built a query based on those csv urls.. I was able to create a web query to fetch around 1,500 rows of data (vs the few rows in the table you helped me with).... and all swiftly fetched in under 90 seconds... Thanks again.