I couldn't get the query top work. Instead of using an internet explorer to
get the data. It puts the results on sheet 1 using Column A and Column B
from sheet 2.


Sub WebQuery()

With Sheets("Sheet1")
.Cells.ClearContents
.Range("A1") = "SYMBOL "
.Range("B1") = "Name of Company"
.Range("C1") = "Date"
.Range("D1") = "Day's Close"
.Range("E1") = "Action"
.Range("F1") = "Shares Held"
.Range("G1") = "Market Value"
.Range("H1") = "Cash Assets"
.Range("I1") = "Total Assets"
.Range("J1") = "ROI(%)"
.Range("K1") = "Recommendations"
RowCount = 2
End With

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

With Sheets("Sheet2")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For ctr = 2 To LastRow
Symbol = .Range("A" & ctr)
Company = .Range("B" & ctr)
URL = "http://www.buzzingstocks.com/in/btest.pl?" & _
"t=" & Symbol & "&f=2"

'get web page
IE.Navigate2 URL
Do While IE.readyState < 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop

Set Tables = IE.document.getElementsByTagname("Table")
'find correct table
tableNum = 0
For Each tbl In Tables
MyStr = "Trading Methodology:"
If Left(tbl.innertext, Len(MyStr)) = MyStr Then
Exit For
End If
tableNum = tableNum + 1
Next tbl
Set DataTable = Tables(tableNum)
Start = False
For Each MyRow In DataTable.Rows
If Start = False Then
If Left(UCase(MyRow.innertext), 4) = _
"DATE" Then

Start = True
End If
Else
If MyRow.innertext = "" Then
Exit For
End If
With Sheets("Sheet1")
.Range("A" & RowCount) = Symbol
.Range("B" & RowCount) = Company
ColCount = 3
For Each Mycol In MyRow.Cells
.Cells(RowCount, ColCount) = _
Mycol.innertext
ColCount = ColCount + 1
Next Mycol
RowCount = RowCount + 1
End With
End If
Next MyRow
Next ctr
End With
End Sub



"Maxi" wrote:

Dear friends,

I need help in a macro

In an excel file, I have a webquery. I wrote a macro to get certain
details from the webpage. url is http://www.buzzingstocks.com/in/btest.pl?t=SUZLON&f=2
(I want to get "Date" "Last Recommendation" "Return on Investment" &
"Annualized returns" for each company

Sheet1 has the web query. name of the query is "btest"
Sheet2 has folowing data
SYMBOL , NAME OF COMPANY , Date , Type , ROI , AR
20MICRONS , 20 Microns Limited , , , ,
3IINFOTECH , 3i Infotech Limited , , , ,
3MINDIA , 3M India Limited , , , ,
AARTIDRUGS , Aarti Drugs Ltd. , , , ,
AARTIIND , Aarti Industries Ltd. , , , ,
AARVEEDEN , Aarvee Denims & Exports Limited , , , ,
ABAN , Aban Offshore Ltd. , , , ,

Above is a sample data, In all I have 1500 rows of data

I want the macro to fill up the columns C D E F

Here is the macro

Sub Macro1()
Dim ctr As Long
For ctr = 2 To 8 'Cells(Rows.Count, "a").End(xlUp).Row
On Error Resume Next
With Sheet1
With .QueryTables("btest")
.Connection = _
"URL;http://www.buzzingstocks.com/in/btest.pl?" _
& "t=" & Cells(ctr, "a") & "&f=2"
End With
Cells(ctr, "c").Value = .Cells(26, "a").Value
Cells(ctr, "d").Value = .Cells(26, "i").Value
Cells(ctr, "e").Value = .Cells(28, "d").Value
Cells(ctr, "f").Value = .Cells(28, "g").Value
End With
Next ctr
ThisWorkbook.Save
End Sub

Somehow it does not fetches entries for few companies. Please help