Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default import data from website

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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default import data from website

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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default import data from website

Dear Joel,

Great, thank you so much !

Cheers
Max
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Dear Joel,Great, thank you so much !CheersMax

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

  #5   Report Post  
Banned
 
Posts: 3
Default

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]













  #6   Report Post  
Junior Member
 
Posts: 10
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Import External data from a Website MichaelRobert Excel Worksheet Functions 6 November 13th 08 03:25 PM
Import data from website everyday at certain time... Jambruins Excel Discussion (Misc queries) 2 November 15th 07 06:32 PM
Import data from a secure website to Excel Matheus Excel Programming 6 October 11th 07 03:11 AM
Import data from a website to excel marsocgm Excel Worksheet Functions 1 July 10th 07 03:45 PM
How do I import data from a SECURED website into Excel? Jimmy Lam Excel Discussion (Misc queries) 0 November 27th 04 05:12 PM


All times are GMT +1. The time now is 04:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"