Hi,
I'm interested to collect table data from HTML page. Is it possible using
excel-vba. If yes, how this can be done.

Thanks,
Mustafa

http://msdn.microsoft.com/library/de...xlWebquery.asp


"Mustafa Ahmad Malik" wrote in message ...
Hi,
I'm interested to collect table data from HTML page. Is it possible using
excel-vba. If yes, how this can be done.

Thanks,
Mustafa

Hi Mustafa,

Here is simple macro to get data from the web, using web query, and saving
the results in a worksheet

sURL = "http://support.microsoft.com/default.aspx"
sParam = "?scid=fh;en-gb;KBHOWTO"
sFullURL = sURL & sParam
With Sheets("Sheet1").QueryTables.Add(Connection:="URL; " & sFullURL , _
Destination:=Sheets("Sheet1").Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mustafa Ahmad Malik" wrote in message
...
Hi,
I'm interested to collect table data from HTML page. Is it possible using
excel-vba. If yes, how this can be done.

Thanks,
Mustafa

Thanks for the input. I need to get a part of a table. Say, table has 10
rows and 10 Col. I need 5x5 table starting from row 4 and col 3 of the
original table.

Thanks
Mustafa

Thanks for the input. I need to get a part of a table. Say, table has 10
rows and 10 Col. I need 5x5 table starting from row 4 and col 3 of the
original table.

Thanks
Mustafa

"Mustafa Ahmad Malik" wrote in message
...
Hi,
I'm interested to collect table data from HTML page. Is it possible using
excel-vba. If yes, how this can be done.

Thanks,
Mustafa

So get it all, and delete the bits you don't want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mustafa Ahmad Malik" wrote in message
...
Thanks for the input. I need to get a part of a table. Say, table has 10
rows and 10 Col. I need 5x5 table starting from row 4 and col 3 of the
original table.

Thanks
Mustafa

"Mustafa Ahmad Malik" wrote in message
...
Hi,
I'm interested to collect table data from HTML page. Is it possible
using
excel-vba. If yes, how this can be done.

Thanks,
Mustafa

and is there a way to get data from a particular cell of HTML table and put
it in Excel sheet cell

Mustafa

"Bob Phillips" wrote in message
...
So get it all, and delete the bits you don't want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mustafa Ahmad Malik" wrote in message
...
Thanks for the input. I need to get a part of a table. Say, table has 10
rows and 10 Col. I need 5x5 table starting from row 4 and col 3 of the
original table.

Thanks
Mustafa

"Mustafa Ahmad Malik" wrote in message
...
Hi,
I'm interested to collect table data from HTML page. Is it possible
using
excel-vba. If yes, how this can be done.

Thanks,
Mustafa

Not something I do, but I fear you may have to parse and find the bits you
want. It seems easier to me to get the lot and delete the unwanted bits. In
your example all you need is

Rows("9:10").EntireRow.Delete
Rows("1:3").EntireRow.Delete
Columns("H:J").EntireColumn.Delete
Columns("A:B").EntireColumn.Delete

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mustafa Ahmad Malik" wrote in message
...
and is there a way to get data from a particular cell of HTML table and
put
it in Excel sheet cell

Mustafa

"Bob Phillips" wrote in message
...
So get it all, and delete the bits you don't want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mustafa Ahmad Malik" wrote in message
...
Thanks for the input. I need to get a part of a table. Say, table has
10
rows and 10 Col. I need 5x5 table starting from row 4 and col 3 of the
original table.

Thanks
Mustafa

"Mustafa Ahmad Malik" wrote in
message
...
Hi,
I'm interested to collect table data from HTML page. Is it possible
using
excel-vba. If yes, how this can be done.

Thanks,
Mustafa

Can you give us the url and the data you want for a test.

--
Don Guillett
SalesAid Software

"Mustafa Ahmad Malik" wrote in message
...
and is there a way to get data from a particular cell of HTML table and
put
it in Excel sheet cell

Mustafa

"Bob Phillips" wrote in message
...
So get it all, and delete the bits you don't want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mustafa Ahmad Malik" wrote in message
...
Thanks for the input. I need to get a part of a table. Say, table has
10
rows and 10 Col. I need 5x5 table starting from row 4 and col 3 of the
original table.

Thanks
Mustafa

"Mustafa Ahmad Malik" wrote in
message
...
Hi,
I'm interested to collect table data from HTML page. Is it possible
using
excel-vba. If yes, how this can be done.

Thanks,
Mustafa

Here is the URL from where the data is to get
http://web.fldoe.org/NCLB/report.cfm...ol&district=31

Here how the output Excel layout should be

ColA ColB ColC ColD ColE
ColF .....
----------------------------------------------------------------------------
-------------------------------------------
DistrictID SchoolID School grade Number of Students AYP Group Tested
95% of the students? 31% Reading at or above grade level? 38% scoring at or
above grade level in Math? Improved performance in Writing by 1%? Increased
Graduation Rate3by 1%? Showed 10% improvement in Reading percent not above
grade level? Showed 10% improvement in Math percent not above grade level?
2003 Y/N 2003 Y/N 2003 Y/N 2002 2003 Y/N 2001 2002 Y/N 2002 2003
Y/N 2002 2003 Y/N
31 0031 C 1518 N Total4 97 Y 36 Y 57 Y 90 84 N 59 73 Y 64
64 44 43
31 0031 C 1518 N White 96 Y 45 Y 66 Y 93 88 N 62 76 Y 55
55 33 34
31 0031 C 1518 N Black 95 Y 11 N 30 N 82 75 N 41 62 Y 88
89 79 70
31 0031 C 1518 N Hispanic 95 Y 18 N 46 Y 81 67 N 67 67 N
85 82 70 54
31 0031 C 1518 N Asian
31 0031 C 1518 N Am. Ind.
31 0031 C 1518 N Econ. 95 Y 17 N 35 N 82 71 N 40 59 Y 82
83 67 65
31 0031 C 1518 N Disadv.



"Don Guillett" wrote in message
...
Can you give us the url and the data you want for a test.

--
Don Guillett
SalesAid Software

"Mustafa Ahmad Malik" wrote in message
...
and is there a way to get data from a particular cell of HTML table and
put
it in Excel sheet cell

Mustafa

"Bob Phillips" wrote in message
...
So get it all, and delete the bits you don't want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mustafa Ahmad Malik" wrote in
message
...
Thanks for the input. I need to get a part of a table. Say, table
has
10
rows and 10 Col. I need 5x5 table starting from row 4 and col 3 of
the
original table.

Thanks
Mustafa

"Mustafa Ahmad Malik" wrote in
message
...
Hi,
I'm interested to collect table data from HTML page. Is it
possible
using
excel-vba. If yes, how this can be done.

Thanks,
Mustafa

Private workbook being sent that uses an imported query table with refresh
and this macro

Sub refreshtable()
Worksheets("Sheet1").QueryTables(1).Refresh _
BackgroundQuery:=False
Columns("f").ColumnWidth = 8
Cells(21, "f").Resize(5, 1).WrapText = False
End Sub


--
Don Guillett
SalesAid Software

"Mustafa Ahmad Malik" wrote in message
...
Here is the URL from where the data is to get
http://web.fldoe.org/NCLB/report.cfm...ol&district=31

Here how the output Excel layout should be

ColA ColB ColC ColD
ColE
ColF .....
--------------------------------------------------------------------------
--
-------------------------------------------
DistrictID SchoolID School grade Number of Students AYP Group Tested
95% of the students? 31% Reading at or above grade level? 38% scoring at
or
above grade level in Math? Improved performance in Writing by 1%?
Increased
Graduation Rate3by 1%? Showed 10% improvement in Reading percent not above
grade level? Showed 10% improvement in Math percent not above grade level?
2003 Y/N 2003 Y/N 2003 Y/N 2002 2003 Y/N 2001 2002 Y/N 2002
2003
Y/N 2002 2003 Y/N
31 0031 C 1518 N Total4 97 Y 36 Y 57 Y 90 84 N 59 73 Y
64
64 44 43
31 0031 C 1518 N White 96 Y 45 Y 66 Y 93 88 N 62 76 Y
55
55 33 34
31 0031 C 1518 N Black 95 Y 11 N 30 N 82 75 N 41 62 Y
88
89 79 70
31 0031 C 1518 N Hispanic 95 Y 18 N 46 Y 81 67 N 67 67 N
85 82 70 54
31 0031 C 1518 N Asian
31 0031 C 1518 N Am. Ind.
31 0031 C 1518 N Econ. 95 Y 17 N 35 N 82 71 N 40 59 Y 82
83 67 65
31 0031 C 1518 N Disadv.



"Don Guillett" wrote in message
...
Can you give us the url and the data you want for a test.

--
Don Guillett
SalesAid Software

"Mustafa Ahmad Malik" wrote in message
...
and is there a way to get data from a particular cell of HTML table
and
put
it in Excel sheet cell

Mustafa

"Bob Phillips" wrote in message
...
So get it all, and delete the bits you don't want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mustafa Ahmad Malik" wrote in
message
...
Thanks for the input. I need to get a part of a table. Say, table
has
10
rows and 10 Col. I need 5x5 table starting from row 4 and col 3 of
the
original table.

Thanks
Mustafa

"Mustafa Ahmad Malik" wrote in
message
...
Hi,
I'm interested to collect table data from HTML page. Is it
possible
using
excel-vba. If yes, how this can be done.

Thanks,
Mustafa

Mustafa

Here's a sub that I used to get box scores from a table in a web page. It
loops through all the elements in the web page and when it finds a table
whose first cell begins with "PLAYER", it gets certain values from the
table. If you know how to get directly to the table, you probably won't
need to loop, but all the objects and properties you need are here. You
need to set a reference to Microsoft Internet Controls.

Sub test()

'http://msdn.microsoft.com/library/default.asp?url=/workshop/browser
'/webbrowser/reference/properties/locationurl.asp
Dim ie As InternetExplorer
Dim doc As Object, Tbl As Object
Dim DocElemsCnt As Long, BoxScoreCnt As Long, RwLen As Long
Dim TbRw As Object

Sheet1.Cells.ClearContents
Set ie = New InternetExplorer
ie.Visible = True

ie.Navigate "http://www.sportsline.com/" & _
"collegebasketball/gamecenter/recap/" & _
"NCAAB_20030405_SYR@TX"
Do
Loop Until ie.Busy = False

Set doc = ie.Document
BoxScoreCnt = 1

For DocElemsCnt = 0 To doc.all.Length - 1
If doc.all.Item(DocElemsCnt).tagname = "TABLE" Then
Set Tbl = doc.all.Item(DocElemsCnt)
If Left(Tbl.innertext, 6) = "PLAYER" Then
For RwLen = 0 To Tbl.Rows.Length - 1
Cells(65536, 1).End(xlUp).Offset(1, 0).Value = _
Tbl.Rows(RwLen).Cells(0).innertext
Cells(65536, 1).End(xlUp).Offset(1, 1).Value = _
Tbl.Rows(RwLen).Cells _
(Tbl.Rows(RwLen).Cells.Length - 1).innertext
Next RwLen

'Debug.Print k
'Cells(j, 1).Value = Tbl.innertext
BoxScoreCnt = BoxScoreCnt + 1
End If
End If
Next DocElemsCnt

ie.Quit

End Sub

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Mustafa Ahmad Malik" wrote in message
...
Here is the URL from where the data is to get
http://web.fldoe.org/NCLB/report.cfm...ol&district=31

Here how the output Excel layout should be

ColA ColB ColC ColD
ColE
ColF .....
--------------------------------------------------------------------------
--
-------------------------------------------
DistrictID SchoolID School grade Number of Students AYP Group Tested
95% of the students? 31% Reading at or above grade level? 38% scoring at
or
above grade level in Math? Improved performance in Writing by 1%?
Increased
Graduation Rate3by 1%? Showed 10% improvement in Reading percent not above
grade level? Showed 10% improvement in Math percent not above grade level?
2003 Y/N 2003 Y/N 2003 Y/N 2002 2003 Y/N 2001 2002 Y/N 2002
2003
Y/N 2002 2003 Y/N
31 0031 C 1518 N Total4 97 Y 36 Y 57 Y 90 84 N 59 73 Y
64
64 44 43
31 0031 C 1518 N White 96 Y 45 Y 66 Y 93 88 N 62 76 Y
55
55 33 34
31 0031 C 1518 N Black 95 Y 11 N 30 N 82 75 N 41 62 Y
88
89 79 70
31 0031 C 1518 N Hispanic 95 Y 18 N 46 Y 81 67 N 67 67 N
85 82 70 54
31 0031 C 1518 N Asian
31 0031 C 1518 N Am. Ind.
31 0031 C 1518 N Econ. 95 Y 17 N 35 N 82 71 N 40 59 Y 82
83 67 65
31 0031 C 1518 N Disadv.



"Don Guillett" wrote in message
...
Can you give us the url and the data you want for a test.

--
Don Guillett
SalesAid Software

"Mustafa Ahmad Malik" wrote in message
...
and is there a way to get data from a particular cell of HTML table
and
put
it in Excel sheet cell

Mustafa

"Bob Phillips" wrote in message
...
So get it all, and delete the bits you don't want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mustafa Ahmad Malik" wrote in
message
...
Thanks for the input. I need to get a part of a table. Say, table
has
10
rows and 10 Col. I need 5x5 table starting from row 4 and col 3 of
the
original table.

Thanks
Mustafa

"Mustafa Ahmad Malik" wrote in
message
...
Hi,
I'm interested to collect table data from HTML page. Is it
possible
using
excel-vba. If yes, how this can be done.

Thanks,
Mustafa

Thanks Dick and Don. That the kind of thing i was looking for.

"Dick Kusleika" wrote in message
...
Mustafa

Here's a sub that I used to get box scores from a table in a web page. It
loops through all the elements in the web page and when it finds a table
whose first cell begins with "PLAYER", it gets certain values from the
table. If you know how to get directly to the table, you probably won't
need to loop, but all the objects and properties you need are here. You
need to set a reference to Microsoft Internet Controls.

Sub test()

'http://msdn.microsoft.com/library/default.asp?url=/workshop/browser
'/webbrowser/reference/properties/locationurl.asp
Dim ie As InternetExplorer
Dim doc As Object, Tbl As Object
Dim DocElemsCnt As Long, BoxScoreCnt As Long, RwLen As Long
Dim TbRw As Object

Sheet1.Cells.ClearContents
Set ie = New InternetExplorer
ie.Visible = True

ie.Navigate "http://www.sportsline.com/" & _
"collegebasketball/gamecenter/recap/" & _
"NCAAB_20030405_SYR@TX"
Do
Loop Until ie.Busy = False

Set doc = ie.Document
BoxScoreCnt = 1

For DocElemsCnt = 0 To doc.all.Length - 1
If doc.all.Item(DocElemsCnt).tagname = "TABLE" Then
Set Tbl = doc.all.Item(DocElemsCnt)
If Left(Tbl.innertext, 6) = "PLAYER" Then
For RwLen = 0 To Tbl.Rows.Length - 1
Cells(65536, 1).End(xlUp).Offset(1, 0).Value = _
Tbl.Rows(RwLen).Cells(0).innertext
Cells(65536, 1).End(xlUp).Offset(1, 1).Value = _
Tbl.Rows(RwLen).Cells _
(Tbl.Rows(RwLen).Cells.Length - 1).innertext
Next RwLen

'Debug.Print k
'Cells(j, 1).Value = Tbl.innertext
BoxScoreCnt = BoxScoreCnt + 1
End If
End If
Next DocElemsCnt

ie.Quit

End Sub

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Mustafa Ahmad Malik" wrote in message
...
Here is the URL from where the data is to get

http://web.fldoe.org/NCLB/report.cfm...ol&district=31

Here how the output Excel layout should be

ColA ColB ColC ColD
ColE
ColF .....

--------------------------------------------------------------------------
--
-------------------------------------------
DistrictID SchoolID School grade Number of Students AYP Group
Tested
95% of the students? 31% Reading at or above grade level? 38% scoring at
or
above grade level in Math? Improved performance in Writing by 1%?
Increased
Graduation Rate3by 1%? Showed 10% improvement in Reading percent not
above
grade level? Showed 10% improvement in Math percent not above grade
level?
2003 Y/N 2003 Y/N 2003 Y/N 2002 2003 Y/N 2001 2002 Y/N 2002
2003
Y/N 2002 2003 Y/N
31 0031 C 1518 N Total4 97 Y 36 Y 57 Y 90 84 N 59 73 Y
64
64 44 43
31 0031 C 1518 N White 96 Y 45 Y 66 Y 93 88 N 62 76 Y
55
55 33 34
31 0031 C 1518 N Black 95 Y 11 N 30 N 82 75 N 41 62 Y
88
89 79 70
31 0031 C 1518 N Hispanic 95 Y 18 N 46 Y 81 67 N 67 67
N
85 82 70 54
31 0031 C 1518 N Asian
31 0031 C 1518 N Am. Ind.
31 0031 C 1518 N Econ. 95 Y 17 N 35 N 82 71 N 40 59 Y
82
83 67 65
31 0031 C 1518 N Disadv.



"Don Guillett" wrote in message
...
Can you give us the url and the data you want for a test.

--
Don Guillett
SalesAid Software

"Mustafa Ahmad Malik" wrote in
message
...
and is there a way to get data from a particular cell of HTML table
and
put
it in Excel sheet cell

Mustafa

"Bob Phillips" wrote in message
...
So get it all, and delete the bits you don't want.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Mustafa Ahmad Malik" wrote in
message
...
Thanks for the input. I need to get a part of a table. Say,
table
has
10
rows and 10 Col. I need 5x5 table starting from row 4 and col 3
of
the
original table.

Thanks
Mustafa

"Mustafa Ahmad Malik" wrote in
message
...
Hi,
I'm interested to collect table data from HTML page. Is it
possible
using
excel-vba. If yes, how this can be done.

Thanks,
Mustafa