Extract data from web
 

HI,

I would like to extract data from below given site through web query. It
needs to input date of query. HOw this can be possible through VBA?. I
tried to record macro and run it but not successful.

The web address is;
http://www.moneycontrol.com/stocks/m...eals/index.php

Thank you for your hlp.

Extract data from web
 

Try this

Sub CNBC()

URL = "http://www.moneycontrol.com/stocks/marketstats/" & _
"blockdeals/index.php"

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


'get web page
IE.navigate2 URL & Ticker
Do While IE.readystate < 4 Or _
IE.busy = True

DoEvents
Loop

Do While IE.document Is Nothing
DoEvents
Loop


ColCount = 1
RowCount = 1

Set Table = IE.document.getelementsbytagname("Table")
For Each tbl In Table
If Left(tbl.innertext, 7) = "BSE/NSE" Then
Set BlockTbl = tbl
End If
Next tbl

For Each Row In BlockTbl.Rows
ColCount = 1
For Each cell In Row.Cells

Cells(RowCount, ColCount) = cell.innertext
ColCount = ColCount + 1
Next cell
RowCount = RowCount + 1
Next Row

IE.Quit
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=173198

Microsoft Office Help

Extract data from web
 

Hi Joel,

Genius.... what a code!! I could not understand head or tail of codes....
from where it takes data from and stores in a variable and writes back into
excel.... superb way of coding. Thanks for your prompt reply.

The codes given by you gives current day's report but if i want to have
previous days data by entering my preferred date and then extract the data
similar to previous one, into excel sheet. HOw can i do it.

Thanks once again.

"joel" wrote:


Try this

Sub CNBC()

URL = "http://www.moneycontrol.com/stocks/marketstats/" & _
"blockdeals/index.php"

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


'get web page
IE.navigate2 URL & Ticker
Do While IE.readystate < 4 Or _
IE.busy = True

DoEvents
Loop

Do While IE.document Is Nothing
DoEvents
Loop


ColCount = 1
RowCount = 1

Set Table = IE.document.getelementsbytagname("Table")
For Each tbl In Table
If Left(tbl.innertext, 7) = "BSE/NSE" Then
Set BlockTbl = tbl
End If
Next tbl

For Each Row In BlockTbl.Rows
ColCount = 1
For Each cell In Row.Cells

Cells(RowCount, ColCount) = cell.innertext
ColCount = ColCount + 1
Next cell
RowCount = RowCount + 1
Next Row

IE.Quit
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=173198

Microsoft Office Help

.

Extract data from web
 

I will take a look toight if there is a way to get previous day data.


You don't need to put data into a variable, you can read data in the
webpage and put it directly into excel. The text is in a property
called innertext on the webpage


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=173198

Microsoft Office Help