MarkH12518
asked on
Compressed Folder is invalid
I have a url that I can go to manually, click on a zip button and download the zip file fine; I can see the file and open it.
But when I try to automate downloading this using VBA in Excel I can download to a MyDocs folder and see that it is there. But when I try to open it I get a message that says "Windows cannot open file. The compressed folder xxxxx is invalid.
I'm in Windows 7 and Excel 2010.
Any ideas? thankx
But when I try to automate downloading this using VBA in Excel I can download to a MyDocs folder and see that it is there. But when I try to open it I get a message that says "Windows cannot open file. The compressed folder xxxxx is invalid.
I'm in Windows 7 and Excel 2010.
Any ideas? thankx
Martin Liss
Can you show the code you use to download the folder? Also just to be sure, what do you mean when you say "when I try to open it"? In other words how are you trying to open it?
ASKER
When I try to open it by either double clicking it or right click>open on the compressed file. I have it on the Windows Compressed file, but I got the same results using WinZip.
This is the actual url i'm going to and you can see the zips there. All of the string paths, etc in the code below are correct.
Thanks for looking.
http://mis.ercot.com/misapp/GetReports.do?reportTypeId=13057&reportTitle=48%20Hour%20Ancillary%20Services%20Reports&showHTMLView=&mimicKey
Public Sub DownloadFile()
Dim objWHTTP As Object
Dim strPath As Variant
Dim strFile As Variant
Dim arrData() As Byte
Dim lngFreeFile As Long
On Error Resume Next
Set objWHTTP = CreateObject("WinHTTP.WinH
If Err.Number <> 0 Then
Set objWHTTP = CreateObject("WinHTTP.WinH
End If
On Error GoTo 0
strPath = Cells(10, 3)
strFile = Cells(12, 3)
objWHTTP.Open "GET", strPath & strFile, False
objWHTTP.Send
arrData = objWHTTP.ResponseBody
If Len(Dir("C:\Users\Mark\Doc
MkDir "C:\Users\Mark\Documents\A
End If
lngFreeFile = FreeFile()
Open "C:\Users\Mark\Documents\A
Put #lngFreeFile, 1, arrData
Close #lngFreeFile
Set objWHTTP = Nothing
Erase arrData
End Sub
Public Sub Download_File2(ByVal vWebFile As String, ByVal vLocalFile As String)
Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as MSXML2.XMLHTTP
Set oXMLHTTP = CreateObject("MSXML2.XMLHT
oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
oXMLHTTP.Send 'send request
'Wait for request to finish
Do While oXMLHTTP.readyState <> 4
DoEvents
Loop
oResp = oXMLHTTP.ResponseBody 'Returns the results as a byte array
'If Len(Dir("C:\Users\Mark\Des
' MkDir "C:\Users\Mark\Desktop\Erc
'End If
Dim strFile As String
strFile = vLocalFile
vLocalFile = "C:\Users\Mark\Desktop\Erc
'Create local file and save results to it
vFF = FreeFile
If Dir(vLocalFile) <> "" Then Kill vLocalFile
Open vLocalFile For Binary As #vFF
Put #vFF, , oResp
Close #vFF
'Clear memory
Set oXMLHTTP = Nothing
End Sub
This is the actual url i'm going to and you can see the zips there. All of the string paths, etc in the code below are correct.
Thanks for looking.
http://mis.ercot.com/misapp/GetReports.do?reportTypeId=13057&reportTitle=48%20Hour%20Ancillary%20Services%20Reports&showHTMLView=&mimicKey
Public Sub DownloadFile()
Dim objWHTTP As Object
Dim strPath As Variant
Dim strFile As Variant
Dim arrData() As Byte
Dim lngFreeFile As Long
On Error Resume Next
Set objWHTTP = CreateObject("WinHTTP.WinH
If Err.Number <> 0 Then
Set objWHTTP = CreateObject("WinHTTP.WinH
End If
On Error GoTo 0
strPath = Cells(10, 3)
strFile = Cells(12, 3)
objWHTTP.Open "GET", strPath & strFile, False
objWHTTP.Send
arrData = objWHTTP.ResponseBody
If Len(Dir("C:\Users\Mark\Doc
MkDir "C:\Users\Mark\Documents\A
End If
lngFreeFile = FreeFile()
Open "C:\Users\Mark\Documents\A
Put #lngFreeFile, 1, arrData
Close #lngFreeFile
Set objWHTTP = Nothing
Erase arrData
End Sub
Public Sub Download_File2(ByVal vWebFile As String, ByVal vLocalFile As String)
Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as MSXML2.XMLHTTP
Set oXMLHTTP = CreateObject("MSXML2.XMLHT
oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
oXMLHTTP.Send 'send request
'Wait for request to finish
Do While oXMLHTTP.readyState <> 4
DoEvents
Loop
oResp = oXMLHTTP.ResponseBody 'Returns the results as a byte array
'If Len(Dir("C:\Users\Mark\Des
' MkDir "C:\Users\Mark\Desktop\Erc
'End If
Dim strFile As String
strFile = vLocalFile
vLocalFile = "C:\Users\Mark\Desktop\Erc
'Create local file and save results to it
vFF = FreeFile
If Dir(vLocalFile) <> "" Then Kill vLocalFile
Open vLocalFile For Binary As #vFF
Put #vFF, , oResp
Close #vFF
'Clear memory
Set oXMLHTTP = Nothing
End Sub
I can't open the link you've posted, but I've always used the Stream Object for downloading files, give this a whirl and see if it works for you:
Sub TestDownload()
DownloadFile "http://www.rondebruin.nl/files/SendMail(Attachment)Testers.zip", "C:\Users\Public\testzip.zip"
End Sub
Sub DownloadFile(url As String, LocalFile As String)
Dim fle() As Byte
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", url
.Send
Do: DoEvents: Loop Until .readystate = 4
fle = .responsebody
With CreateObject("ADODB.Stream")
.Open
.Type = 1
.write fle
.Position = 0
.SaveToFile LocalFile
.Close
End With
.Abort
End With
End SubASKER
Thanks. I'm still getting the same result. Windows cannot open. Compressed file is invalid.
Frustrating.
Mark
Frustrating.
Mark
hmmm very strange - does the above code work for you with the TestDownload() zip?
ASKER
no, i used that code on my last effort. I had a Microsoft tell me that all the data retrieved in .responsebody is string, but i tried converting and got the same result. thx
ResponseBody isn't technically a string, it's a byte array. ResponseText is the string version
It's really odd that the zip file from the site I posted doesn't work, it works fine on my computer. I could understand it if the code from the link you posted didn't work since it's inconceivable that it might want cookies passing to it before it allows you to download.
One thing to try would be to Debug.Print the response text and see if it returning something other than a zip file, my guess would be that this is what's happening, you're naming it as a zip file but it isn't really so windows can't open it
It's really odd that the zip file from the site I posted doesn't work, it works fine on my computer. I could understand it if the code from the link you posted didn't work since it's inconceivable that it might want cookies passing to it before it allows you to download.
One thing to try would be to Debug.Print the response text and see if it returning something other than a zip file, my guess would be that this is what's happening, you're naming it as a zip file but it isn't really so windows can't open it
ASKER
yeah, i get a bunch of this with debug.print on fle
??????????????????????????
???????????6??????????????
??????????????????????????
???????
??????????????????????????
???????????6??????????????
??????????????????????????
???????
No, don't debug.print fle.
You need to debug.print the response text, using your url:
You need to debug.print the response text, using your url:
Sub DownloadFile(url As String, LocalFile As String)
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", url
.Send
Do: DoEvents: Loop Until .readystate = 4
debug.print .responsetext
.Abort
End With
End SubASKER
Hey, no you're file did work. It did open up and work correctly. It's mine that won't work.
Yes, that's what I thought. try the above code and see what you get. I'll bet it dumps a load of HTML
or actually, just change the extension of the zip file you've tried to download from .zip to .txt and see if you can open it with notepad
What are you trying to do with this code?
What that says in English is "create the object and if you get an error then create it anyhow" which doesn't make sense to me.
Try it without the On Error Resume Next. What happens? Is there an error?
On Error Resume Next
Set objWHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
If Err.Number <> 0 Then
Set objWHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
End If
On Error GoTo 0What that says in English is "create the object and if you get an error then create it anyhow" which doesn't make sense to me.
Try it without the On Error Resume Next. What happens? Is there an error?
ASKER
Ok, if I download it and call it a .txt file I get something I can open. But I'm wide open here I guess. How do I download programmatically and get access to the csv files that are inside the zip folders here?
thx
http://mis.ercot.com/misapp/GetReports.do?reportTypeId=13057&reportTitle=48%20Hour%20Ancillary%20Services%20Reports&showHTMLView=&mimicKey
thx
http://mis.ercot.com/misapp/GetReports.do?reportTypeId=13057&reportTitle=48%20Hour%20Ancillary%20Services%20Reports&showHTMLView=&mimicKey
Well it sounds like you need to pass cookies/some authentication to download from the site.
I can't access the link at all, it just times out. So I can't really give you any more specific help than that. What's the output in the textfile?
I can't access the link at all, it just times out. So I can't really give you any more specific help than that. What's the output in the textfile?
ASKER
That's the debug.print result in the immediate window
I meant when you open the texfile
ASKER
Here it is
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>ERCOT MIS</title>
<meta http-equiv="Content-Type" content="text/html"; charset="utf-8">
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control"
<meta http-equiv="expires" content="0">
<base href="http://mis.ercot.com:80/misapp/">
<link href="/misapp/css/vdg.css"
<script language="javascript" src="/misapp/scripts/portl
</head>
<body>
<form name="PortletTypeAForm" method="GET" action="/misapp/GetReports
<input type="hidden" name="basePath" id ="basePath" value="http://mis.ercot.com:80/misapp/"/>
<table width='800' border='0' cellpadding='0' cellspacing='0' class='tbl_ltTaupe'><tr><t
</form>
</body>
</html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>ERCOT MIS</title>
<meta http-equiv="Content-Type" content="text/html"; charset="utf-8">
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control"
<meta http-equiv="expires" content="0">
<base href="http://mis.ercot.com:80/misapp/">
<link href="/misapp/css/vdg.css"
<script language="javascript" src="/misapp/scripts/portl
</head>
<body>
<form name="PortletTypeAForm" method="GET" action="/misapp/GetReports
<input type="hidden" name="basePath" id ="basePath" value="http://mis.ercot.com:80/misapp/"/>
<table width='800' border='0' cellpadding='0' cellspacing='0' class='tbl_ltTaupe'><tr><t
</form>
</body>
</html>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok I'll try that. Thx a lot
ASKER
turns out i need a certificate and some other stuff i was't aware of,
but checked as solution
thanks a lot for all your help
Mark
but checked as solution
thanks a lot for all your help
Mark