Dear All,
I try to download an excel file from a website and i only get it back after going through a redirect page that contains a wait in javascript.
I use the xmlhttp object for this as it turned out the result is the same using
- msinet control
- excel's workbooks.open
- inet dll
My code is like
Dim oXMLHTTP As object ' MSXML2.XMLHTTP26
On Error GoTo errhandle
Set oXMLHTTP = CreateObject("Microsoft.XM
oXMLHTTP.Open "GET", myUrl, False
oXMLHTTP.send
' return bytearray
strhtml = oXMLHTTP.responseText
Set oXMLHTTP = Nothing
Exit Function
errhandle:
' do something here
Set oXMLHTTP = Nothing
the strhtml returns a page with the redirect to itself i do not know how to wait for the timeout or get the file in another way
tried to catch the download file dialog from IE, but couldn't get that working either with the findwindow API
hope someone can help me to get this working.
Thanks for your time
Brian
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
thanks Paul,
that certainly looked like the trick but it does not work
i give you the url i'm trying to download
http://excel2.smarthouse.d
i encounter on machines that have not downloaded that file through the browser the problem of not being able to download the xls but the timeout page instead
of course on each request the key at the end is different so it works at times but i suspect not when the file is not yet cached from a previous request
regards
Brian
Try this method:
Option Explicit
Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Private Sub Command1_Click()
Dim myURL As String
myURL = "http://excel2.smarthouse.
URLDownloadToFile 0, myURL, "C:\temp\test.xls", 0, 0
End Sub
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Vontobel Excel Writer</title>
</head>
<body onload="window.setTimeout(
<table border="0" width="100%" height="100%">
<tr>
<td valign="middle" align="center"><img src="images/derinet_de_pre
</tr>
</table>
</body>
<script language="javascript">
<!--
function redirect() {
location.href = location.href;
}
// -->
</script>
</html>
at first it worked for me too, but after i emptied the ie downloaded files and cache and it started to go against me
it started working again but when it tried it on a different pc it left me in the cold
Hi Paul,
Thanks for the idea of the loop. I also looked further on the net and found the winhttp dll that contains a request object with a redirect property. however, its not working as i expected and still needed the loop and pause (this is a sleep function that convert to seconds). After 3 days on this problem i still hold my breath to see if it still works on a different machine but it looks that it finally is solved. This is my code for anyone interested (needs a reference to the mentioned dll)
Private Function DownloadFile(ByVal myURL As String) As Variant
Dim myReq As WinHttpRequest, hFile, bContentIsOK As Boolean
On Error GoTo errhandle
bContentIsOK = False
Do Until bContentIsOK
Set myReq = CreateObject("WinHttp.WinH
myReq.Option(WinHttpReques
myReq.setTimeouts 30000, 30000, 30000, 30000
myReq.Open "GET", myURL, True
myReq.send
myReq.waitForResponse (100000)
' return bytearray
If Len(myReq.responseBody) <> 245 Then
bContentIsOK = True
Else
DoEvents
Sleep 5000 'However long the javascript wait is
End If
Loop
DownloadFile = myReq.responseBody
Set myReq = Nothing
Exit Function
errhandle:
' do something here
Set myReq = Nothing
errCount = 1
ErrMsgAdd "xml http part", "Error in getting file from the web." & myURL, Err.Number, Err.Description, errdata
Err.Clear
Set myReq = Nothing
End Function
Brian
Business Accounts
Answer for Membership
by: PaulHewsPosted on 2007-12-11 at 06:27:01ID: 20449488
The redirect page must have a string you can test for, so that you can tell you don't have the right content.
Select allOpen in new window