Need help with macro

[Maxi]

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

 

[joel]

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]

I get error "Object variable or With block variable not set" on line :
For Each MyRow In DataTable.Rows ????

 

[joel]

Try the macro with the original companies that you posted. i think the
problem is one of the compnies in your list doesn't exist or is returning bad
data. The code worked perfectly on my PC at home. At work I'm having a
problem but I think it is due to the latest updates are not installed on my
PC at work. Let me know which company isn't working. Go to the website and
verify that the company Symbol is a valid symbol.

 

[Walter Briscoe]

In message <[email protected]> of Tue,
5 May 2009 16:17:01 in microsoft.public.excel.programming, joel

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()
[snip]

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

IE.Navigate2 URL

Joel's demonstration code is REALLY interesting to me.

I run Excel 2003 with IE7 on Windows XP SP2.
A second machine has a similar system, but with Windows XP SP3.

I think I have a configuration issue in IE.

At "IE.Visible = True", an instance of IE is shown with Favorites open;
on the 2nd machine Favorites is not opened.

"IE.Navigate2 URL" results in a 2nd instance of IE being opened -
probably a child of the 1st - and IE.readyState retains a value of
READYSTATE_UNINITIALIZED. On the 2nd machine, the URL is opened in the
1st instance and IE.readyState soon goes to READYSTATE_COMPLETE which I
infer is 4.

What should I do to correct this problem?
Where should I read about the methods (and properties) of the
"InternetExplorer.Application" object?

Thanks!

 

[joel]

Walter: I experience the same problem yesterda with READYSTATE_UNINITIALIZED
(3). I wrote th e macro on my PC and home which has a version of excel
2002/2003 and it worked. I tried the code at work which has a newer verion
of excel which is 2003 but doesn't have all the updates from microsoft and
got the READYSTATE_UNINITIALIZED problem. I assumed the problem was due to
some updates that was missing.

This website is weird. The table numbers and data vary each time you run
the code. I needed to get the table starting with the string "Trading
Methodology:". Normally when I run code like this the table number is fixed.
this time I found sometimes the "Trading Methodology:" table was table
number 10 and sometimes it was 11. So I added code to the macro to actually
search for the string in all the tables. I used INSTR to find the string and
found it appear in the middle of one of the tables and was at the beginning
of anothe table. I then switched from using INSTR() to LEFT() = "Trading
Methodology:".

I don't have permission at work to download upgrades from microsoft.con. If
you can try getting all the upgrades and service packs and see if the problem
disappears.

I had to go to microsoft.com and search for READYSTATE_UNINITIALIZED to find
out the value was 3. I still can't get the code to run on my pc at work. I
have to comment out the test for IE.readyState and step through the code to
get it too work. You could add a time and wait for 5 seconds instead of the
test.

In message <[email protected]> of Tue,
5 May 2009 16:17:01 in microsoft.public.excel.programming, joel

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()
[snip]

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

IE.Navigate2 URL

Joel's demonstration code is REALLY interesting to me.

I run Excel 2003 with IE7 on Windows XP SP2.
A second machine has a similar system, but with Windows XP SP3.

I think I have a configuration issue in IE.

At "IE.Visible = True", an instance of IE is shown with Favorites open;
on the 2nd machine Favorites is not opened.

"IE.Navigate2 URL" results in a 2nd instance of IE being opened -
probably a child of the 1st - and IE.readyState retains a value of
READYSTATE_UNINITIALIZED. On the 2nd machine, the URL is opened in the
1st instance and IE.readyState soon goes to READYSTATE_COMPLETE which I
infer is 4.

What should I do to correct this problem?
Where should I read about the methods (and properties) of the
"InternetExplorer.Application" object?

Thanks!

 

[Walter Briscoe]

In message <[email protected]> of Thu,
7 May 2009 03:10:01 in microsoft.public.excel.programming, joel

Joel,

Walter: I experience the same problem yesterda with READYSTATE_UNINITIALIZED
(3). I wrote th e macro on my PC and home which has a version of excel
2002/2003 and it worked. I tried the code at work which has a newer verion
of excel which is 2003 but doesn't have all the updates from microsoft and
got the READYSTATE_UNINITIALIZED problem. I assumed the problem was due to
some updates that was missing.

This website is weird. The table numbers and data vary each time you run
the code. I needed to get the table starting with the string "Trading
Methodology:". Normally when I run code like this the table number is fixed.
this time I found sometimes the "Trading Methodology:" table was table
number 10 and sometimes it was 11. So I added code to the macro to actually
search for the string in all the tables. I used INSTR to find the string and
found it appear in the middle of one of the tables and was at the beginning
of anothe table. I then switched from using INSTR() to LEFT() = "Trading
Methodology:".

I don't have permission at work to download upgrades from microsoft.con. If
you can try getting all the upgrades and service packs and see if the problem
disappears.

I had to go to microsoft.com and search for READYSTATE_UNINITIALIZED to find
out the value was 3. I still can't get the code to run on my pc at work. I
have to comment out the test for IE.readyState and step through the code to
get it too work. You could add a time and wait for 5 seconds instead of the
test.

In message <[email protected]> of Tue,
5 May 2009 16:17:01 in microsoft.public.excel.programming, joel

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()
[snip]

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

IE.Navigate2 URL

Joel's demonstration code is REALLY interesting to me.

I run Excel 2003 with IE7 on Windows XP SP2.
A second machine has a similar system, but with Windows XP SP3.

I think I have a configuration issue in IE.

At "IE.Visible = True", an instance of IE is shown with Favorites open;
on the 2nd machine Favorites is not opened.

"IE.Navigate2 URL" results in a 2nd instance of IE being opened -
probably a child of the 1st - and IE.readyState retains a value of
READYSTATE_UNINITIALIZED. On the 2nd machine, the URL is opened in the
1st instance and IE.readyState soon goes to READYSTATE_COMPLETE which I
infer is 4.

What should I do to correct this problem?
Where should I read about the methods (and properties) of the
"InternetExplorer.Application" object?

Thanks!

 

[Walter Briscoe]

In message <[email protected]> of Thu,
7 May 2009 03:10:01 in microsoft.public.excel.programming, joel

Joel,
Thanks for a quick response. The problem I have on one system is with IE
rather than a particular URL. CreateObject + IE.Visible = TRUE opens one
window. IE.Navigate2 URL opens a second window. i.e. the second window
has no obvious connection with the first. I take it the problem is with
my Internet Explorer configuration rather than anything else. I compared
tools\internet options - particularly focussing on the advanced tab -
eliminated all differences and nothing changed. My problem is not with
the site. It is that the URL is opened in a child of the object rather
than in the object. I am inclined to install IE8 and see if the problem
goes away.

Walter: I experience the same problem yesterda with READYSTATE_UNINITIALIZED
(3). I wrote th e macro on my PC and home which has a version of excel
2002/2003 and it worked. I tried the code at work which has a newer verion
of excel which is 2003 but doesn't have all the updates from microsoft and
got the READYSTATE_UNINITIALIZED problem. I assumed the problem was due to
some updates that was missing.

This website is weird. The table numbers and data vary each time you run
the code. I needed to get the table starting with the string "Trading
Methodology:". Normally when I run code like this the table number is fixed.
this time I found sometimes the "Trading Methodology:" table was table
number 10 and sometimes it was 11. So I added code to the macro to actually
search for the string in all the tables. I used INSTR to find the string and
found it appear in the middle of one of the tables and was at the beginning
of anothe table. I then switched from using INSTR() to LEFT() = "Trading
Methodology:".

I don't have permission at work to download upgrades from microsoft.con. If
you can try getting all the upgrades and service packs and see if the problem
disappears.

I had to go to microsoft.com and search for READYSTATE_UNINITIALIZED to find
out the value was 3. I still can't get the code to run on my pc at work. I
have to comment out the test for IE.readyState and step through the code to
get it too work. You could add a time and wait for 5 seconds instead of the
test.

In message <[email protected]> of Tue,
5 May 2009 16:17:01 in microsoft.public.excel.programming, joel

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()
[snip]

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

IE.Navigate2 URL

Joel's demonstration code is REALLY interesting to me.

I run Excel 2003 with IE7 on Windows XP SP2.
A second machine has a similar system, but with Windows XP SP3.

I think I have a configuration issue in IE.

At "IE.Visible = True", an instance of IE is shown with Favorites open;
on the 2nd machine Favorites is not opened.

"IE.Navigate2 URL" results in a 2nd instance of IE being opened -
probably a child of the 1st - and IE.readyState retains a value of
READYSTATE_UNINITIALIZED. On the 2nd machine, the URL is opened in the
1st instance and IE.readyState soon goes to READYSTATE_COMPLETE which I
infer is 4.

What should I do to correct this problem?
Where should I read about the methods (and properties) of the
"InternetExplorer.Application" object?

Thanks!

 

[joel]

I would go to the Office Developer - Web component group and ask the
question. let me know what you find. My suspecions is it has to do wiith an
Active X component. When I get the READYSTATE_UNINITIALIZED there is still
one object that need to be downloaded on the webpage which is an ad ware
object. I think somehow you are loosing the connection between and excel and
the IE that is causing a 2nd window to open or an active X is interfering
with the process.

In message <[email protected]> of Thu,
7 May 2009 03:10:01 in microsoft.public.excel.programming, joel

Joel,
Thanks for a quick response. The problem I have on one system is with IE
rather than a particular URL. CreateObject + IE.Visible = TRUE opens one
window. IE.Navigate2 URL opens a second window. i.e. the second window
has no obvious connection with the first. I take it the problem is with
my Internet Explorer configuration rather than anything else. I compared
tools\internet options - particularly focussing on the advanced tab -
eliminated all differences and nothing changed. My problem is not with
the site. It is that the URL is opened in a child of the object rather
than in the object. I am inclined to install IE8 and see if the problem
goes away.

Walter: I experience the same problem yesterda with READYSTATE_UNINITIALIZED
(3). I wrote th e macro on my PC and home which has a version of excel
2002/2003 and it worked. I tried the code at work which has a newer verion
of excel which is 2003 but doesn't have all the updates from microsoft and
got the READYSTATE_UNINITIALIZED problem. I assumed the problem was due to
some updates that was missing.

This website is weird. The table numbers and data vary each time you run
the code. I needed to get the table starting with the string "Trading
Methodology:". Normally when I run code like this the table number is fixed.
this time I found sometimes the "Trading Methodology:" table was table
number 10 and sometimes it was 11. So I added code to the macro to actually
search for the string in all the tables. I used INSTR to find the string and
found it appear in the middle of one of the tables and was at the beginning
of anothe table. I then switched from using INSTR() to LEFT() = "Trading
Methodology:".

I don't have permission at work to download upgrades from microsoft.con. If
you can try getting all the upgrades and service packs and see if the problem
disappears.

I had to go to microsoft.com and search for READYSTATE_UNINITIALIZED to find
out the value was 3. I still can't get the code to run on my pc at work. I
have to comment out the test for IE.readyState and step through the code to
get it too work. You could add a time and wait for 5 seconds instead of the
test.

In message <[email protected]> of Tue,
5 May 2009 16:17:01 in microsoft.public.excel.programming, joel
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()

[snip]

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

[snip]

IE.Navigate2 URL

Joel's demonstration code is REALLY interesting to me.

I run Excel 2003 with IE7 on Windows XP SP2.
A second machine has a similar system, but with Windows XP SP3.

I think I have a configuration issue in IE.

At "IE.Visible = True", an instance of IE is shown with Favorites open;
on the 2nd machine Favorites is not opened.

"IE.Navigate2 URL" results in a 2nd instance of IE being opened -
probably a child of the 1st - and IE.readyState retains a value of
READYSTATE_UNINITIALIZED. On the 2nd machine, the URL is opened in the
1st instance and IE.readyState soon goes to READYSTATE_COMPLETE which I
infer is 4.

What should I do to correct this problem?
Where should I read about the methods (and properties) of the
"InternetExplorer.Application" object?

Thanks!

 

[Walter Briscoe]

In message <[email protected]> of Thu,
7 May 2009 07:18:17 in microsoft.public.excel.programming, joel

I have added microsoft.public.office.developer.web.components to my list
of subscribed newsgroups and will kibitz there for a while before daring
to ask my question.
Thank you for your good will.

I would go to the Office Developer - Web component group and ask the
question. let me know what you find. My suspecions is it has to do wiith an
Active X component. When I get the READYSTATE_UNINITIALIZED there is still
one object that need to be downloaded on the webpage which is an ad ware
object. I think somehow you are loosing the connection between and excel and
the IE that is causing a 2nd window to open or an active X is interfering
with the process.

In message <[email protected]> of Thu,
7 May 2009 03:10:01 in microsoft.public.excel.programming, joel

Joel,
Thanks for a quick response. The problem I have on one system is with IE
rather than a particular URL. CreateObject + IE.Visible = TRUE opens one
window. IE.Navigate2 URL opens a second window. i.e. the second window
has no obvious connection with the first. I take it the problem is with
my Internet Explorer configuration rather than anything else. I compared
tools\internet options - particularly focussing on the advanced tab -
eliminated all differences and nothing changed. My problem is not with
the site. It is that the URL is opened in a child of the object rather
than in the object. I am inclined to install IE8 and see if the problem
goes away.

Walter: I experience the same problem yesterda with READYSTATE_UNINITIALIZED
(3). I wrote th e macro on my PC and home which has a version of excel
2002/2003 and it worked. I tried the code at work which has a newer verion
of excel which is 2003 but doesn't have all the updates from microsoft and
got the READYSTATE_UNINITIALIZED problem. I assumed the problem was due to
some updates that was missing.

This website is weird. The table numbers and data vary each time you run
the code. I needed to get the table starting with the string "Trading
Methodology:". Normally when I run code like this the table number
is fixed.
this time I found sometimes the "Trading Methodology:" table was table
number 10 and sometimes it was 11. So I added code to the macro to actually
search for the string in all the tables. I used INSTR to find the
string and
found it appear in the middle of one of the tables and was at the beginning
of anothe table. I then switched from using INSTR() to LEFT() = "Trading
Methodology:".

I don't have permission at work to download upgrades from microsoft.con. If
you can try getting all the upgrades and service packs and see if
the problem
disappears.

I had to go to microsoft.com and search for READYSTATE_UNINITIALIZED to find
out the value was 3. I still can't get the code to run on my pc at work. I
have to comment out the test for IE.readyState and step through the code to
get it too work. You could add a time and wait for 5 seconds instead of the
test.

:

In message <[email protected]> of Tue,
5 May 2009 16:17:01 in microsoft.public.excel.programming, joel
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()

[snip]

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

[snip]

IE.Navigate2 URL

Joel's demonstration code is REALLY interesting to me.

I run Excel 2003 with IE7 on Windows XP SP2.
A second machine has a similar system, but with Windows XP SP3.

I think I have a configuration issue in IE.

At "IE.Visible = True", an instance of IE is shown with Favorites open;
on the 2nd machine Favorites is not opened.

"IE.Navigate2 URL" results in a 2nd instance of IE being opened -
probably a child of the 1st - and IE.readyState retains a value of
READYSTATE_UNINITIALIZED. On the 2nd machine, the URL is opened in the
1st instance and IE.readyState soon goes to READYSTATE_COMPLETE which I
infer is 4.

What should I do to correct this problem?
Where should I read about the methods (and properties) of the
"InternetExplorer.Application" object?

Thanks!

 

[Maxi]

Dear Joel,

I was unwell... could not reply. I still get the same error. I checked
my companies, they are correct and returns output on the website. A
strange thing is that when I get the error "Object variable or With
block variable not set" on line : For Each MyRow In DataTable.Rows, I
click "Debug" -> go to the vba code, shift the yellow arrow little
above and keep it on the line Set DataTable = Tables(tableNum) then I
press F8 till it crosses the line For Each MyRow In DataTable.Rows
then I press F5 and everything works

This happens lot of times. If you want, I can send you the list of all
1272 companies in an attachment.

Thanks

 

[Maxi]

I tried with the original companies 20MICRONS 3IINFOTECH 3MINDIA
AARTIDRUGS AARTIIND AARVEEDEN ABAN

Still the same error. Please help.

 

[Patrick Molloy]

error "Object variable or With block variable not set"
means that you probably either haven't DIM'd a variable correctly or haven't
created the object

the statement

For Each MyRow In DataTable.Rows
requires that the variable named MyRow is a range, so you must have either
DIM MyRow as Range ' PREFERRED
or
DIM MyRow '(better than not having it), it defaults to Variant

and at the top of the code module have
OPTION EXPLICIT
this forces you to DIM all vatiables, and is great for spotting typos.
Also, you get to use intellisence where you dim objects specifically


Aside from MyRow, the error could be that DataTable doesn't exist.
I assume that it too is a range object?
so you must have
DIM DataTable as Range
and
SET DataTable = range(???)


in both, DIM allocates memory while SET creates the object.