| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import data from Web cvs format
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
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 ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
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" wrote in message ... 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 ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
Thanks Tim
-- Regards Ron de Bruin http://www.rondebruin.nl "Tim Williams" <timjwilliams at gmail dot com wrote in message ... 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" wrote in message ... 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 ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
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/micro...a0066ddaf19815 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" wrote in message ... Thanks Tim -- Regards Ron de Bruin http://www.rondebruin.nl "Tim Williams" <timjwilliams at gmail dot com wrote in message ... 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" wrote in message ... 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 ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
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 ... 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/micro...a0066ddaf19815 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" wrote in message ... Thanks Tim -- Regards Ron de Bruin http://www.rondebruin.nl "Tim Williams" <timjwilliams at gmail dot com wrote in message ... 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" wrote in message ... 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 ... 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
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" wrote in message ... 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 ... 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/micro...a0066ddaf19815 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" wrote in message ... Thanks Tim -- Regards Ron de Bruin http://www.rondebruin.nl "Tim Williams" <timjwilliams at gmail dot com wrote in message ... 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" wrote in message ... 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 ... 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
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 ... 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" wrote in message ... 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 ... 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/micro...a0066ddaf19815 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" wrote in message ... Thanks Tim -- Regards Ron de Bruin http://www.rondebruin.nl "Tim Williams" <timjwilliams at gmail dot com wrote in message ... 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" wrote in message ... 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 ... 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
Many thanks for everyone who replied to my original post!!!! Your help is so much appreciated!!!! -- dundik ------------------------------------------------------------------------ dundik's Profile: http://www.excelforum.com/member.php...o&userid=27344 View this thread: http://www.excelforum.com/showthread...hreadid=468530 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
Hi Tim
See http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... 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 ... 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" wrote in message ... 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 ... 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/micro...a0066ddaf19815 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" wrote in message ... Thanks Tim -- Regards Ron de Bruin http://www.rondebruin.nl "Tim Williams" <timjwilliams at gmail dot com wrote in message ... 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" wrote in message ... 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 ... 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 |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import data from Web cvs format
Ron,
Thanks for putting that together: a useful resource. Tim -- Tim Williams Palo Alto, CA "Ron de Bruin" wrote in message ... Hi Tim See http://www.rondebruin.nl/windowsxpzip.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... 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 ... 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" wrote in message ... 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 ... 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/micro...a0066ddaf19815 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" wrote in message ... Thanks Tim -- Regards Ron de Bruin http://www.rondebruin.nl "Tim Williams" <timjwilliams at gmail dot com wrote in message ... 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" wrote in message ... 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 ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| import data format | Excel Worksheet Functions | |||
| Import Data From Landscape Format | New Users to Excel | |||
| Import data format, need help | Excel Discussion (Misc queries) | |||
| Import data as number format | Excel Discussion (Misc queries) | |||
| Import data not in correct format | Excel Discussion (Misc queries) | |||
Linear Mode
