I want to retrieve a table from a website. The problem is that the Table
on this site is attached in cvs format. What code do I write to get the
Internet open this file and import data into my own Excel spreadsheet?
Any help will be appreciated highly. Thanks.


--
dundik
------------------------------------------------------------------------
dundik's Profile: http://www.excelforum.com/member.php...o&userid=27344
View this thread: http://www.excelforum.com/showthread...hreadid=468530

Can you give us your version of excel and the url

--
Don Guillett
SalesAid Software

"dundik" wrote in
message ...

I want to retrieve a table from a website. The problem is that the Table
on this site is attached in cvs format. What code do I write to get the
Internet open this file and import data into my own Excel spreadsheet?
Any help will be appreciated highly. Thanks.


--
dundik
------------------------------------------------------------------------
dundik's Profile:
http://www.excelforum.com/member.php...o&userid=27344
View this thread: http://www.excelforum.com/showthread...hreadid=468530

http://www.bankofcanada.ca/en/rates/yield_curve.html

file "Retrieve all file" (zip format). Excel 2002.

Many thanks fir hints!!!!!


--
dundik
------------------------------------------------------------------------
dundik's Profile: http://www.excelforum.com/member.php...o&userid=27344
View this thread: http://www.excelforum.com/showthread...hreadid=468530

Since it's in zip format you'll have to download the file and unzip it
first, before importing it into Excel. There's no way you can directly open
a zip file in Excel.

Tim

--
Tim Williams
Palo Alto, CA


"dundik" wrote in
message ...

http://www.bankofcanada.ca/en/rates/yield_curve.html

file "Retrieve all file" (zip format). Excel 2002.

Many thanks fir hints!!!!!


--
dundik
------------------------------------------------------------------------
dundik's Profile:
http://www.excelforum.com/member.php...o&userid=27344
View this thread: http://www.excelforum.com/showthread...hreadid=468530

This might get you started....
Requires an empty folder "files" in the same folder as the workbook
running the code.
Tim.


Option Explicit

Sub FetchUnzipOpen()
Dim s, sz 'don't dim these as strings-must be variants!
s = ThisWorkbook.Path & "\files"
sz = s & "\test.zip"
FetchFile "http://www.bankofcanada.ca/stat/zcz_31052005.zip", sz
Unzip s, sz
'now you just need to open the csv file....

End Sub


Sub FetchFile(sURL As String, sPath)
Dim oXHTTP As Object
Dim oStream As Object

Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
Set oStream = CreateObject("ADODB.Stream")
Application.StatusBar = "Fetching " & sURL & " as " & sPath
oXHTTP.Open "GET", sURL, False
oXHTTP.send
With oStream
.Type = 1 'adTypeBinary
.Open
.Write oXHTTP.responseBody
.SaveToFile sPath, 2 'adSaveCreateOverWrite
.Close
End With
Set oXHTTP = Nothing
Set oStream = Nothing
Application.StatusBar = False

End Sub

Sub Unzip(sDest, sZip)
Dim o
Set o = CreateObject("Shell.Application")
o.NameSpace(sDest).CopyHere o.NameSpace(sZip).Items
End Sub

"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
Since it's in zip format you'll have to download the file and unzip
it
first, before importing it into Excel. There's no way you can
directly open
a zip file in Excel.

Tim

--
Tim Williams
Palo Alto, CA


"dundik" wrote
in
message ...

http://www.bankofcanada.ca/en/rates/yield_curve.html

file "Retrieve all file" (zip format). Excel 2002.

Many thanks fir hints!!!!!


--
dundik
------------------------------------------------------------------------
dundik's Profile:
http://www.excelforum.com/member.php...o&userid=27344
View this thread:
http://www.excelforum.com/showthread...hreadid=468530

I should have noted that the unzipping part is XP-only.

However Ron has some code for other cases:
http://www.rondebruin.nl/zip.htm

Tim

"Tim Williams" <saxifrax@pacbell*dot*net wrote in message
...
This might get you started....
Requires an empty folder "files" in the same folder as the workbook
running the code.
Tim.


Option Explicit

Sub FetchUnzipOpen()
Dim s, sz 'don't dim these as strings-must be variants!
s = ThisWorkbook.Path & "\files"
sz = s & "\test.zip"
FetchFile "http://www.bankofcanada.ca/stat/zcz_31052005.zip", sz
Unzip s, sz
'now you just need to open the csv file....

End Sub


Sub FetchFile(sURL As String, sPath)
Dim oXHTTP As Object
Dim oStream As Object

Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
Set oStream = CreateObject("ADODB.Stream")
Application.StatusBar = "Fetching " & sURL & " as " & sPath
oXHTTP.Open "GET", sURL, False
oXHTTP.send
With oStream
.Type = 1 'adTypeBinary
.Open
.Write oXHTTP.responseBody
.SaveToFile sPath, 2 'adSaveCreateOverWrite
.Close
End With
Set oXHTTP = Nothing
Set oStream = Nothing
Application.StatusBar = False

End Sub

Sub Unzip(sDest, sZip)
Dim o
Set o = CreateObject("Shell.Application")
o.NameSpace(sDest).CopyHere o.NameSpace(sZip).Items
End Sub

"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
Since it's in zip format you'll have to download the file and unzip
it
first, before importing it into Excel. There's no way you can
directly open
a zip file in Excel.

Tim

--
Tim Williams
Palo Alto, CA


"dundik"
wrote in
message ...

http://www.bankofcanada.ca/en/rates/yield_curve.html

file "Retrieve all file" (zip format). Excel 2002.

Many thanks fir hints!!!!!


--
dundik
------------------------------------------------------------------------
dundik's Profile:
http://www.excelforum.com/member.php...o&userid=27344
View this thread:
http://www.excelforum.com/showthread...hreadid=468530