Posts by analystbank

    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


    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


    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


    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


    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

    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: Delete Rows, Based on Date


    What should be the macro to take 'Date' from a user (code should prompt user to give specific date) or read date in Cell O2, and delete entire ROWS from Column O, where similar date is found, including Row 2 where the code read the date from cell O2, or taken from a user through prompt.

    Re: Compare each cell in Range with each other with 6 alternates, and highlight


    cytop, Yes, from him only, very precise solution and speedy execution pf code.


    Thanks for looking in here. I'll take care not to post in 2-3 forums.


    I'll take care of the rules, with due respect. If offended, apology.


    I started this thread on Aug 21, and after 23 DAYS, i asked for help there, and i got reply very next day there.


    I know the seniors r too busy, and this forum is also resource rich.

    My current data sheet looks as under; (Sample File is attached)

    [TABLE="width: 505"]

    [tr]


    [td]

    Row

    [/td]


    [td]

    ColumnK

    [/td]


    [td]

    ColumnL

    [/td]


    [td]

    ColumnM

    [/td]


    [td]

    ColumnN

    [/td]


    [td]

    ColumnO

    [/td]


    [/tr]


    [tr]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    21-Sep-2013

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    27-Sep-2013

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    03-Oct-2013

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    11-Oct-2013

    [/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    23-Oct-2013

    [/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    17-Sep-2013

    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    01-Oct-2013

    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    08-Oct-2013

    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    23-Oct-2013

    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    12

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    29-Sep-2013

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    13

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    11-Oct-2013

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    14

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    13-Oct-2013

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    15

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]

    20-Oct-2013

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    16

    [/td]


    [td]


    [/td]


    [td]

    24-Sep-2013

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    17

    [/td]


    [td]


    [/td]


    [td]

    21-Oct-2013

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    18

    [/td]


    [td]

    21-Sep-2013

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    19

    [/td]


    [td]

    23-Sep-2013

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    20

    [/td]


    [td]

    02-Oct-2013

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    21

    [/td]


    [td]

    13-Oct-2013

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    22

    [/td]


    [td]

    22-Oct-2013

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [tr]


    [td]

    23

    [/td]


    [td]

    26-Oct-2013

    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [td]


    [/td]


    [/tr]


    [/TABLE]

    I want a macro that would start reading each cells in Column K from Cell K3 (till last row say K100), and do following;

    1) If cell is Empty in ColumnK (say K3), move down to next cell (say K4) in ColumnK
    2) When Data is found in above case, say K18, it should compare
    either a) Exact date (i.e. 21Sep13), or
    b) 1 day after (22Sep13), or
    c) 2 day after (23Sep13) OR
    d) 1 day before (20Sep13) , or
    e) 2 day before (19Sep13)

    in each cell in Column L


    When date is found per above criteria in ColumnL, the cell should be highlighted either by COLOR or BOLD

    3) Then counter should is moved down to Cell K19 (immediate cell below K18) and repeat the process as given in 2

    4) This process should be repeated till Cell K100 which is the last range.


    For illustration purpose, I’ve manually highlighted Cell K22 (Date 22Oct13) and L17 (having date 21Oct13), which is just one day before as explained in 2(d) above.

    There could be case, where cell has date that meets criteria more than once, I guess, that should not be the problem, as formatting (COLOR or BOLD) could be overwritten, and last hit will always be highlighted.

    So, the first comparison is between Column K and Column L, and that is where I need your help.

    I will repeat the process later for comparison between Column L and Column M, and then Column M, and N and so on, BUT FIRST COLUMN COMPARISON between ColumnK, and ColumnL, I need help.