Extract Data Table information from Java xHTML page

  • Attached is sample workbook having two sheets. Sheet URL, has Web page link in Cell B2.


    Kindly have a look at it in Browser to know how the data is structured/presented. Please Right click on the browser to view the source code of the page, which reveals that it uses some xhtml JSP (java server page), perhaps reason why the conventional way of importing web query does not work.


    I want a VBA macro that would read URL from Sheet URL Cell B2, and bring the data in Sheet (named) Web Range B2.


    Conventional code is as under


    When I run above, what happens all the Table Data comes in single cell B2, with delimiter like ',' etc and it is not correctly placed within the table, as it should have been. Making it difficult to read, or process.


    SAMPLE FILE IS ATTACHED having run the above macro.

  • Re: Extract Data Table information from JSP xHTML page


    analystbank
    you cant add simple links!
    The solution for others that may have a similar problem
    With the Microscoft Reference library
    referenced in the VBE > Tools> References>"Microsoft HMTL Library"



  • Re: Extract Data Table information from Java xHTML page


    analystbank


    This is not the first time you have been warned about cross posting. In fact in this post you promised not to do it again. Obviously you can remember where you get free help but not how to behave on the Forums. It's not too difficult to post links to other Forums where you ask the same question, next time that you can't be bothered you will be stopped from posting here.

  • Re: Extract Data Table information from Java xHTML page


    may also help

  • Re: Extract Data Table information from Java xHTML page


    APOLOGY FOR THE INCONVENIENCE, it was genuine error due to facts explained. no such repeat would ever happen from my side.


    Thanks for all the support, will revert back once i test the revised code.

  • Re: Extract Data Table information from Java xHTML page


    Sir, (Pike, Esq)


    I tried your code given in #5 above after activating referenced in the VBE > Tools> References>"Microsoft HMTL Library"


    Now when Code is getting executed, at line

    Code
    For Each levelOne In .Children



    I am getting error code as


    "Run Time Error 91 : Object Variable or With Block Variable not set"


    Also, Can u please suggest, instead of giving url in Code itself, can we tell VBA to read from a specific sheet, Cell reference?


    I am using Excel 97-2003 Workbook

  • Re: Extract Data Table information from Java xHTML page


    Quote from analystbank;708776

    APOLOGY FOR THE INCONVENIENCE, it was genuine error due to facts explained. no such repeat would ever happen from my side.


    Thanks for all the support, will revert back once i test the revised code.


    Deja vu - virtually the same response in your previous post.

  • Re: Extract Data Table information from Java xHTML page


    Thread on same subject was posted here (http://www.excelforum.com/exce…-data-table-new-post.html) by my friend, which has now been closed.


    pike, thanks for your help.


    The code suggested by you just brings in few of the data contained in one Table from the page. Other adjacent data are not coming within excel.


    If this thread is not close, may i ask you for specific issues, now?

  • Re: Extract Data Table information from Java xHTML page


    Currently I am using the code given in # 5, above.

    Improvement needed –
    Instead of giving URL directly inside the code,

    Code
    With CreateObject("msxml2.xmlhttp")
            .Open "GET", "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuoteFO.jsp?underlying=ACC&instrument=FUTSTK&expiry=24APR2014&type=-&strike=-", False
            .send
            htm.body.innerHTML = .responseText
        End With


    kindly make it read from a cell from different sheet,
    say URL is given in Sheets(“EURL”).Range(“F18”)
    Sheets(“EURL”).Range(“F19”)
    Sheets(“EURL”).Range(“F20”) and so on (say I’ve 3 URL)

    And output (data content from web page table) should be pasted on
    Sheets(“Fut”) starting from cell b2
    Sheets(“Fut”) starting from cell b50
    Sheets(“Fut”) starting from cell b50

    Respectively, leave gap of about 50 rows between two data set, so that, I can perform some calculation later.





    In the given screen image [Blocked Image: http://i62.tinypic.com/34rjg5s.jpg] I am giving the numbers to sub-table, content whereof, I require within excel.

    Code in #5 above, currently brings data given in Table 3.

    I also require data that is being part of Tab1, Tab2, Tab 4, Tab5, and Tab 6, alongwith the title given to it either on top (horizontal box/table), and left side (vertical box/table)

  • Re: Extract Data Table information from Java xHTML page


    At the out set, i say, I know very basics of VBA.


    I run the macro that you so nicely made in post (#) 5 above. The code brings in table 3 shown above (i guess, Tab9 of xhtml page).


    But I require Table content of all table numerically marked in blue font above.


    I tried altering code line

    Code
    With .getElementById("tab9Content")

    which has reference to tab content but no success, as i guess, each table has different row and columns.


    P.S.: Other observation is that, when i run this macro after couple of minutes, it brings same data, albeit, the webpage table content is different, and keeps changing, after couple of minutes.

  • Re: Extract Data Table information from Java xHTML page


    well i help to get the data you can work out the rest ..one reqest per thread
    its a tricky site
    but with the "Microsoft internet control library" refereneced the data can be expunged

    just looking at the tag combinations to see the best layout

  • Re: Extract Data Table information from Java xHTML page


    Sure, Sir. I am trying to study the code, and also the structure of xhtml page.


    When I am running the revised code Sub Stock_Data(), I am getting Compile error :User Defined type not defined, when variable is defined at line


    Code
    Dim oIE As InternetExplorer


    My only query was to get all Table(Tab) information in excel, hope no offence, as this was the only request. If i am able to get all table data, i will then proceed to work further, taking clue from you. Thanks for support and assistance, Sir.


    Am I missing something on Tools->References, this time again? Microsoft HTML Object Library is already selected.


    My default browser is Google Chrome. I also have Mozilla, and Internet Explorer.

  • Re: Extract Data Table information from Java xHTML page


    try...


    you may have to add the timer .. depending on your local internet service

  • Re: Extract Data Table information from Java xHTML page


    Thanks, Sir. with reference to post #18


    Again data under TAB 'Fundamentals', (TAB5Content of xhtml line number- starting from 604) which appears on Left hand side, and Top header, which gives, Open, High, Low, last close, (line 586 onward) does not appear.


    Moreover, If i run the macro, i just get the header names, not the numeric data. I am using Excel 2003 version. When I opened the attachment, above, it nicely brings in numeric data inside the table, except FUNDAMENTALS (TAB5Content of xhtml line number- starting from 604) , and latest Open, High, Low, and last traded price of the stock.


    I am still impressed with the code that you gave in # 5. And really scratching my head, as to how do i navigate through (TAB5Content of xhtml line number- starting from 604) and also get data from line 586 onward that has Open, High, Low, last traded price, alter Array and try to get it in excel.

    I am more concern with data Tab5Content and Open, High, Low, which is the latest information, which requires further analysis.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!