Hi all,
I need to get a solution for following problem.

I need to extract a table from a website, like this
http://www.cbot.com/cbot/pub/page/0,3181,1213,00.html and get it into a
useful format
Already tried it with the usual "import website" functions in Excel, but
neither is it working everytime, nor is it possible to add the data instead
of overwriting them all the time.

At the same time I would prefer it to delete unused rows and cells in the
spreadsheet after the import, but this is a minor issue.

It would be nice, if the format could look like this in the end.

17-Sep 16-Sep 15-Sep
24964 25131 25519
27450 27350 27200
27450 27350 27200

Every help is appreciated & thanks in advance

brgds
Max

Sub GetQuotes()

CR = Chr(13)

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

URL = "http://www.cbot.com/cbot/pub/page/0,3181,1213,00.html"

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

Do While ie.busy = True
DoEvents
Loop
Set cTable = ie.Document.getElementsByTagname("table")
'get all rows excep last row which contains the data
''Table starts at row 0, subtract two to ignore last row
ColCount = 1
For TableRow = 0 To (cTable(0).Rows.Length - 2)
Set TRow = cTable(0).Rows(TableRow)
RowCount = 1
For Each cell In TRow.Cells
Cells(RowCount, ColCount) = _
Trim(Replace(cell.innertext, CR, ""))
RowCount = RowCount + 1
Next cell
ColCount = ColCount + 1
Next TableRow

End Sub


"Maximilian Harmstorf" wrote:

Hi all,
I need to get a solution for following problem.

I need to extract a table from a website, like this
http://www.cbot.com/cbot/pub/page/0,3181,1213,00.html and get it into a
useful format
Already tried it with the usual "import website" functions in Excel, but
neither is it working everytime, nor is it possible to add the data instead
of overwriting them all the time.

At the same time I would prefer it to delete unused rows and cells in the
spreadsheet after the import, but this is a minor issue.

It would be nice, if the format could look like this in the end.

17-Sep 16-Sep 15-Sep
24964 25131 25519
27450 27350 27200
27450 27350 27200

Every help is appreciated & thanks in advance

brgds
Max

Dear Joel,

Great, thank you so much !

Cheers
Max

Joel...
Can you tell me where to place this code... hot to do this....

thank you!

On Friday, October 24, 2008 12:27 AM Maximilian Harmstorf wrote:

Hi all,
I need to get a solution for following problem.

I need to extract a table from a website, like this
http://www.cbot.com/cbot/pub/page/0,3181,1213,00.html and get it into a
useful format
Already tried it with the usual "import website" functions in Excel, but
neither is it working everytime, nor is it possible to add the data instead
of overwriting them all the time.

At the same time I would prefer it to delete unused rows and cells in the
spreadsheet after the import, but this is a minor issue.

It would be nice, if the format could look like this in the end.

17-Sep 16-Sep 15-Sep
24964 25131 25519
27450 27350 27200
27450 27350 27200

Every help is appreciated & thanks in advance

brgds
Max


On Friday, October 24, 2008 6:17 AM Joe wrote:

Sub GetQuotes()

CR = Chr(13)

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

URL = "http://www.cbot.com/cbot/pub/page/0,3181,1213,00.html"

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

Do While ie.busy = True
DoEvents
Loop
Set cTable = ie.Document.getElementsByTagname("table")
'get all rows excep last row which contains the data
''Table starts at row 0, subtract two to ignore last row
ColCount = 1
For TableRow = 0 To (cTable(0).Rows.Length - 2)
Set TRow = cTable(0).Rows(TableRow)
RowCount = 1
For Each cell In TRow.Cells
Cells(RowCount, ColCount) = _
Trim(Replace(cell.innertext, CR, ""))
RowCount = RowCount + 1
Next cell
ColCount = ColCount + 1
Next TableRow

End Sub


"Maximilian Harmstorf" wrote:


On Saturday, October 25, 2008 11:46 AM max.harmstor wrote:

Dear Joel,

Great, thank you so much !

Cheers
Max


Submitted via EggHeadCafe
LINQ executed in Parallel (PLINQ)
http://www.eggheadcafe.com/tutorials...lel-plinq.aspx

Cho thuê vÃ* lắp đặt sân khấu chuyên nghiệp tại TPHCM -LH 0937 150 220

Ä�ạt PhÆ°Æ¡ng - Ä�Æ¡n vị chuyên cung cấp, cho thuê âm thanh - ánh sáng - cung cấp, lắp đặt sân khấu phục vụ sá»± kiện chuyên nghiệp. ... Chuyên thiết kế, cung cấp vÃ* lắp đặt hệ thống thiết bị âm thanh, ánh sáng, không gian, ...sân khấu. Liên hệ Mr Ä�ạt 0911 000 222



[center]

gặp Ä‘i Bui chá»— chị Nguyen Thu Nguyen Jessica Bùi gá»�i :v. em Máº*p Gấu vá»� đấy. chồng ăn Gam Ä�ang vợ phá Ä‘áº*p nay Tâm cả Huyen trÆ°a Má»›i Gá»�i kêu xong
Bao hiem Suc Khoe Daiichi