Hi I want to pull real time data from following links to excel.I have tried web query but it is not working .
http://www.nseindia.com/live_m…/equities_stock_watch.htm
http://www.nseindia.com/live_m…atch/live_index_watch.htm
any other method to pull data to excel ?
thanks
regards
web query not return table values
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
-
-
-
Re: web query not working
Hi RAMLAL123,
Welcome to the forum .. with jave scripted sites that use <SPAN>
The easiest but slowest way would be some thing like .Code
Display MoreDim 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/live_index_watch.htm" 'oIE.Navigate "http://www.teamrankings.com/mlb/stat/runs-per-game" oIE.Visible = True Do Until (oIE.ReadyState = 4 And Not oIE.Busy) DoEvents Loop Application.Wait (Now + TimeValue("0:00:03")) HTMLDoc.body.innerHTML = oIE.Document.body.innerHTML With HTMLDoc.body Set objElementsTd = .getElementsByTagName("td") End With lRow = 1 For Each objTd In objElementsTd Debug.Print objTd.innerText Cells(lRow, 1).value = objTd.innerText lRow = lRow + 1 Next End Sub
-
Re: web query not working
Thanks pike plz check attached file ,I can't get data
thanks
regards -
Re: web query not return table values
Hi RAMLAL123,
Code
Display More'This VBA project requires the following references (in Tools -> References): 'Microsoft Internet Controls 'Microsoft HTML Office Library Sub webtable() 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/live_index_watch.htm" Do Until (oIE.ReadyState = 4 And Not oIE.Busy) DoEvents Loop Application.Wait (Now + TimeValue("0:00:03")) HTMLDoc.body.innerHTML = oIE.Document.body.innerHTML With HTMLDoc.body 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("Sheet1").Cells(ActRw + r + 1, c + 1) = elemcollection(t).Rows(r).Cells(c).innerText Next c Next r ActRw = ActRw + elemcollection(t).Rows.Length + 1 Next t End With oIE.Quit End Sub
-
Re: web query not return table values
Thanks pike
going through the threads to learn excel
how can I set automatic refresh after every 1 minute ?
thanks
regards -
-
Re: web query not return table values
Quote'This VBA project requires the following references (in Tools -> References):'Microsoft Internet Controls
'Microsoft HTML Office Library
Sub webtable()
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/live_index_watch.htm"
Do Until (oIE.ReadyState = 4 And Not oIE.Busy)
DoEvents
Loop
Application.Wait (Now + TimeValue("0:00:03"))
HTMLDoc.body.innerHTML = oIE.Document.body.innerHTML
With HTMLDoc.body
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("Sheet1").Cells(ActRw + r + 1, c + 1) = elemcollection(t).Rows(r).Cells(c).innerText
Next c
Next r
ActRw = ActRw + elemcollection(t).Rows.Length + 1
Next t
End With
oIE.Quit
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:01:00"), "webtable"
End Sub
I have added Private sub hope it works
regards -
Re: web query not return table values
Have a look at this thread to run macro at regular interval
http://www.ozgrid.com/forum/showthread.php?t=10625
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!