Extract Data Table information from Java xHTML page

analystbank · Apr 23rd 2014

Re: Extract Data Table information from Java xHTML page

Thanks, for all the efforts, Sir. Somehow, does not work, and bring in the data. I close this thread, but sincerely appreciate your efforts, to solve this query.

I would look for some alternates, which could be easier, and that being non Web related query but on this subject only, would start a new thread, here. Trust, no offence.

Thanks a ton, once again.

**pike** · Apr 23rd 2014

Re: Extract Data Table information from Java xHTML page

are you using the workbook i have attached?
it has all the library reference checked.. it very strange you dont get any thing at all

**pike** · Apr 23rd 2014

Re: Extract Data Table information from Java xHTML page

the two non-table list added..

Code

Sub Stock_Data()
Dim HTMLDoc As New HTMLDocument
Dim objElementsTd As Object
Dim objTd As Object
Dim lRow As Long
Dim myarray()
Dim oIE As InternetExplorer
Set oIE = New InternetExplorer
oIE.navigate "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuoteFO.jsp?underlying=ACC&instrument=FUTSTK&expiry=24APR2014&type=-&strike=-"
oIE.Visible = True
Do Until (oIE.readyState = 4 And Not oIE.Busy)
DoEvents
Loop
'Application.Wait (Now + TimeValue("0:00:03"))'may have to adjust depending on local systems
HTMLDoc.body.innerHTML = oIE.document.body.innerHTML
oIE.Quit
With HTMLDoc.body
 Set elemcollection = .getElementsByTagName("ul")
 For Each TagColl In elemcollection
 If InStr(1, TagColl.innerHTML, "LI><SPAN") > 0 Then
For Each Tagitem In Split(TagColl.innerText, Chr(10))
rRow = rRow + 1
  ThisWorkbook.Sheets("Stock Results").Cells(r + 1 + rRow, C + 1) = Trim(Tagitem)
  Next
  End If
Next
 Set elemcollection = .getElementsByTagName("table")
 For t = 0 To (elemcollection.Length - 1)


            For r = 0 To (elemcollection(t).Rows.Length - 1)


                For C = 0 To (elemcollection(t).Rows(r).Cells.Length - 1)
   
                    ThisWorkbook.Sheets("Stock Results").Cells(r + 1 + rRow, C + 1) = Trim(elemcollection(t).Rows(r).Cells(C).innerText)


                Next C
               
            Next r
            rRow = Sheets("Stock Results").Cells(Rows.Count, 1).End(xlUp).Row + 1
        Next t
End With
End Sub

Display More

analystbank · Apr 23rd 2014

Re: Extract Data Table information from Java xHTML page

No success.

But thanks for all your efforts. I'll try to get it in Ecel 2007. Give sometime for feedback.

Quote

are you using the workbook i have attached?
it has all the library reference checked.. it very strange you dont get any thing at all

Yes, I did. I use the same workbook, as you shared. When I downloaded the workbook, that you shared, i could see, all numeric data also, but when i run the query, it just returned text part.

I just get the header names, Text portion. All numerical data just does not appear. Could it be due to version difference. Currently, I m using excel 2003.

**pike** · Apr 23rd 2014

Re: Extract Data Table information from Java xHTML page

Are you referencing libraries ?..

analystbank · Apr 25th 2014

Re: Extract Data Table information from Java xHTML page

Quote

Are you referencing libraries ?..

Whatever you suggested, I activated but not working sir. Macro does run without errror, and returns text portion (mainly data header from web page) but it does not bring in respective numerical value.

I tried using Excel 2007 also, but same issue.

Further, given the quantum of more data to bring in and further processing involved, it would not be prudent to open IE browser, and bring excel data for different URLs. Just sharing my thought based on facts. Thanks.

**pike** · Apr 25th 2014

Re: Extract Data Table information from Java xHTML page

Hello analystbank,
can you attach the workbook you are using?

analystbank · Apr 28th 2014

Re: Extract Data Table information from Java xHTML page

Sorry for the delayed reply, Sir.

Here is the file. Nothing changed, except Web page URL, due to change in month.

**pike** · Apr 28th 2014

Re: Extract Data Table information from Java xHTML page

Hi analystbank
I have no answer as i have just down loaded the workbook and run the macro and I get the results from the web page

analystbank · Apr 28th 2014

Re: Extract Data Table information from Java xHTML page

this is really strange. In one of the Sheet named 'Stock Results', i kept the output also, what i received, which is just text.

Extract Data Table information from Java xHTML page

Participate now!

Share