Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Import data from Web cvs format

15 views
Skip to first unread message

dundik

unread,
Sep 17, 2005, 4:09:43 PM9/17/05
to

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?action=getinfo&userid=27344
View this thread: http://www.excelforum.com/showthread.php?threadid=468530

Don Guillett

unread,
Sep 17, 2005, 5:38:28 PM9/17/05
to
Can you give us your version of excel and the url

--
Don Guillett
SalesAid Software
don...@281.com
"dundik" <dundik.1viuaa_...@excelforum-nospam.com> wrote in
message news:dundik.1viuaa_...@excelforum-nospam.com...

dundik

unread,
Sep 17, 2005, 6:34:00 PM9/17/05
to

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

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

Many thanks fir hints!!!!!

Tim Williams

unread,
Sep 18, 2005, 1:32:58 PM9/18/05
to
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" <dundik.1vizuc_...@excelforum-nospam.com> wrote in
message news:dundik.1vizuc_...@excelforum-nospam.com...

Tim Williams

unread,
Sep 18, 2005, 7:42:26 PM9/18/05
to
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
news:uz5CEcHv...@TK2MSFTNGP09.phx.gbl...

Tim Williams

unread,
Sep 18, 2005, 7:46:20 PM9/18/05
to
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
news:ug2AgqKv...@TK2MSFTNGP09.phx.gbl...

Tim Williams

unread,
Sep 19, 2005, 6:31:39 PM9/19/05
to
Ron,
Just for completeness - I have not tested the code at the end of this thread
but it seems to provide a "component-free" method to create a zip file and
add contents.

http://groups.google.com/group/microsoft.public.scripting.vbscript/browse_frm/thread/8856e35111efd887/20a0066ddaf19815?lnk=st&q=vbscript+zip+xp+%22shell.application%22&rnum=1&hl=en#20a0066ddaf19815

It's for vbscript but could most likely be easily adapted for VB/VBA: I'll
try it out when out of work hours...

Regards,
Tim.

--
Tim Williams
Palo Alto, CA


"Ron de Bruin" <ronde...@kabelfoon.nl> wrote in message
news:Or%23lJQTv...@TK2MSFTNGP12.phx.gbl...
> Thanks Tim
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl


>
>
> "Tim Williams" <timjwilliams at gmail dot com> wrote in message

news:uX$tYGTvF...@tk2msftngp13.phx.gbl...
> > Ron,
> >
> > I got that from the vbscript newsgroup - I don't think I saw an
equivalent
> > useage for zipping.
> >
> > Regards


> > Tim
> >
> > --
> > Tim Williams
> > Palo Alto, CA
> >
> >

> > "Ron de Bruin" <ronde...@kabelfoon.nl> wrote in message
> > news:Ojxd0fSv...@TK2MSFTNGP09.phx.gbl...
> >> Hi Tim


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

> >> Do you have code for Zipping also.
> >> I never try it because I use Winzip myself
> >>
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl


> >>
> >>
> >> "Tim Williams" <saxifrax@pacbell*dot*net> wrote in message

> > news:upNsrsKv...@TK2MSFTNGP10.phx.gbl...

Ron de Bruin

unread,
Sep 19, 2005, 10:39:21 AM9/19/05
to
Hi Tim

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

Do you have code for Zipping also.


I never try it because I use Winzip myself

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Tim Williams" <saxifrax@pacbell*dot*net> wrote in message news:upNsrsKv...@TK2MSFTNGP10.phx.gbl...

Tim Williams

unread,
Sep 19, 2005, 11:48:34 AM9/19/05
to
Ron,

I got that from the vbscript newsgroup - I don't think I saw an equivalent
useage for zipping.

Regards
Tim

--
Tim Williams
Palo Alto, CA

"Ron de Bruin" <ronde...@kabelfoon.nl> wrote in message
news:Ojxd0fSv...@TK2MSFTNGP09.phx.gbl...

Ron de Bruin

unread,
Sep 19, 2005, 12:05:50 PM9/19/05
to
Thanks Tim

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Tim Williams" <timjwilliams at gmail dot com> wrote in message news:uX$tYGTvF...@tk2msftngp13.phx.gbl...

Ron de Bruin

unread,
Sep 20, 2005, 12:10:56 AM9/20/05
to
Hi Tim

Will look at it also after work

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Tim Williams" <timjwilliams at gmail dot com> wrote in message news:O$cYonWvF...@TK2MSFTNGP15.phx.gbl...

Tim Williams

unread,
Sep 20, 2005, 1:12:16 AM9/20/05
to
Ron,

Works for me. XL2002, Win XP Pro.

Regards,
Tim

Sub TestZip()

ZipStuff ThisWorkbook.Path & "\sourceFiles", _
ThisWorkbook.Path & "\new.zip"

End Sub

Sub ZipStuff(SourceFolder, ZipPath)
Dim oApp, oFolder

NewZip ZipPath 'create a new zip file

Set oApp = CreateObject("Shell.Application")
'Copy the files to the compressed folder
Set oFolder = oApp.NameSpace(SourceFolder)
If Not oFolder Is Nothing Then
oApp.NameSpace(ZipPath).CopyHere oFolder.Items
End If

End Sub

Sub NewZip(sPath)

Dim oFSO, arrHex, sBin, i, Zip
Set oFSO = CreateObject("Scripting.FileSystemObject")

arrHex = Array(80, 75, 5, 6, 0, 0, 0, _
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

For i = 0 To UBound(arrHex)
sBin = sBin & Chr(arrHex(i))
Next

With oFSO.CreateTextFile(sPath, True)
.Write sBin
.Close
End With

End Sub

"Ron de Bruin" <ronde...@kabelfoon.nl> wrote in message

news:ej9gVlZv...@TK2MSFTNGP12.phx.gbl...

Ron de Bruin

unread,
Sep 20, 2005, 11:20:29 AM9/20/05
to
Hi Tim

Is working for me in 2002 also
I will test in 2000 and 2003 also and will add some example code to my webpage about this.

Thanks for the newsgoup link


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Tim Williams" <saxifrax@pacbell*dot*net> wrote in message news:uvKfkHav...@TK2MSFTNGP09.phx.gbl...

dundik

unread,
Sep 20, 2005, 5:43:38 PM9/20/05
to

Many thanks for everyone who replied to my original post!!!! Your help
is so much appreciated!!!!


--
dundik
------------------------------------------------------------------------

Ron de Bruin

unread,
Sep 23, 2005, 9:11:36 AM9/23/05
to
Hi Tim

See
http://www.rondebruin.nl/windowsxpzip.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" <ronde...@kabelfoon.nl> wrote in message news:OlY%23ebfvF...@TK2MSFTNGP10.phx.gbl...

Tim Williams

unread,
Sep 23, 2005, 9:29:36 PM9/23/05
to
Ron,

Thanks for putting that together: a useful resource.

Tim

--
Tim Williams
Palo Alto, CA


"Ron de Bruin" <ronde...@kabelfoon.nl> wrote in message

news:OVlrYBEw...@TK2MSFTNGP12.phx.gbl...

0 new messages