VBA - Can't get to download a file

[Ooalkman]

Hi there !

I would like to download a file from a website using VBA. The tricky part is that the file is generated after filling a form in the webpage and clicking on the submit button, so I don't know its name and address before download (meaning I can't use the URLDownloadToFile API). I've tried several things, so far I've got two problems :

1. Using a XMLHTTP request (as in this thread), I can't get to do the right request to trigger the download. I think the dynamic form is reading the webpage before submitting, adding some variables and I don't know how to simulate that with my request.
2. Using a HTMLDocument with IE, I manage to trigger the download but I can't download automatically, I have to interfere manually (and I'd like the app to run background which doesn't seem possible this way).

So anybody has a clue on this ? I have to admit i'm getting a little depressed.

 

[Kyle123]

What's the url?

 

[Ooalkman]

I am not authorized to share it (and it requires authentification to reach the page, which I manage to do), but it is a .do webpage.
Here's a screen (btw, I'm French) :

So as I was saying, I have to fill the form then click on "Export". The javascript code triggered on click is the following :

document.getElementById('isExport').value=1;
document.getElementById('mbReloadOption').value=false;
document.getElementById('msModeSearch').value='submitExport';
document.getElementById('planningMForm').submit();

Those are all parameters (and I write them in my request when I try the XMLHTTP thing) except for the "isExport" one which is a hidden input element without a name (but is initially worth 1 anyway).

 

[Kyle123]

You can't easily use XMLHTTP since it won't automatically handle cookies for you - that will be the reason you can't get it working. Without access, I can't really give you any more help other than the following:

1. Use the WINHTTP library instead of XMLHTTP, it is the modern version and handles cookies
2. First you need to get an authentication cookie for WINHTTP, so the first thing you need to do is create a log in request, assuming this is successful, you'll have a cookie
3. Having the cookie will allow you to download the document, so then make a second request as you tried before, this should get you the document data - once you have it you'll need to write it in a binary stream to the hard disk, the easiest way of doing this is with ADODB.Stream - there are a lot of examples of doing this about.

 

[Ooalkman]

I am gonna try this but I think I already managed to authenticate in my code since when I try my request and save it in a .html file (with an ADOB.Stream object indeed) it gives me the same page I screened earlier. Here is an extract of the code :

Dim myURL As String
myURL = "https://addresstoauthenticate.com"
Dim WinHttpReq As Object, oStream As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "POST", myURL, False
WinHttpReq.send "USER=USER&PASSWORD=PASSWORD"

If WinHttpReq.Status = 200 Then 'and it usually is
    WinHttpReq.Open "POST", "https://o2.grdf.fr/planningM.do", False
    WinHttpReq.send ("mlEntitySlc=1290&mlSiteSlc=-1&msSelectedDate=23%2F10%2F2014&msType=M&miOptionHour=&miOptionDay=0&msDate=20141023&msModeSearch=submitExport&msComplInfo=&msOptionId=&mbReloadOption=false&mlFormIndspPkPers=&mlFormIndspPkIndispo=&mlFormIndspPkNew=&msFormIndspDate=&msFormIndspHBegin=&msFormIndspHEnd=&msFormIndspComment=&mlFormIndspReport=-1&miNbWeeks=1&mbPkGroup%5B0%5D=on&mlPkGroup%5B0%5D=5196&mbPkGroup%5B1%5D=on&mlPkGroup%5B1%5D=5205&mbPkGroup%5B2%5D=on&mlPkGroup%5B2%5D=5207&mbPkGroup%5B3%5D=on&mlPkGroup%5B3%5D=5211&mbPkGroup%5B4%5D=on&mlPkGroup%5B4%5D=5215&mbPkGroup%5B5%5D=on&mlPkGroup%5B5%5D=5243&mlPkIndispoForRpt=-1&msReportDate=&msReportComments=")
    If WinHttpReq.Status = 200 Then
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        oStream.Type = 1
        oStream.Write WinHttpReq.responseBody
        oStream.SaveToFile "D:\file.html", 2 ' 1 = no overwrite, 2 = overwrite
        oStream.Close
    End If
End If

And I get the same page I screened before.

So I am gonna try the WINHTTP and feedback but i can't do it right now. In first view, it seems to be a little more complicated than XMLHTTP, so would you have by any chance a good tutorial for me ?

 

[Kyle123]

You just need to change:

Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")

To:

Set WinHttpReq = CreateObject("WINHTTP.WinHTTPRequest.5.1")

To use Winhttp, everything else is the same.

 

[Ooalkman]

Ok so I tried it and it didn't change anything.
Maybe I can give you the address and the pass by e-mail ?

 

[Ooalkman]

Yes ! Finally I think I managed to download the file !
It was because of the headers that I didn't set. u_u

So I THINK it worked because I have to wait for the same amount of time I have to when I download the file manually, but I'm not really sure as my file is all... Binary, I guess ? Here is the look of it :

Here is my last code :

Sub DownloadFile()

Dim myURL As String, req As String
req = "GAIA_ERRAUTH=0;"
req = req & "SMSESSION=9PmBQ3yuR6f6Xk+EitjK/X6mvTZokx0kSFXRlaIKltLEAgjRdOhPyENqgTQdaJUc4+ZjvtaqD0AwhLogx8P2+Rz3GWAwjeWVISEJ6zXAdSsamkB/v2GpOZiQ6HPRog/1k+tlpUOtPyu3fiAUfx/KJjOFE9YIJK29/jpw2juxyvR1WYODWC2x4eeDG2munKML/AKEVY2B7YNjnLOeQNlrVTljfYs0zcr/zFf9DuCBeIFeWtVB3H0RNd0T1EticfYe7VsCSOhBsyMNC9mGQhb8oEmyOiH3x8jijUTyKItAgdnqVjqDTLo5rBJgb0BxjXSMrY5WasnHE18i45YgvydDj+oi7DDuJx4jc4pMme4BFdRCIMt5mTWBeIKnQ3BBoTwwbeeYseYvPyg4BOqcexECZ+lu1iyJRpNWv8/OcSxhyCEZeXo1TNw+Q/UBroTTBJRt/WmvnnEDqbkNMgtJj+siX6fb1JJIYk5/klvPcUt7fZXAbBLTE/7DK5iQthpImp9xRF34U8w+fG37vNlglcSzoOKF65SYdDz4KuCRVfNtEj1BwBwMN6ZCKq2Wyuvr/ZXEKJU/zkRi9ObzNYACFXOQauKWUafLjbWV+ZM+Rlg3OISpgmTA783Dy7waX7aZGEyeeQ2TmWE8sX/RgQNogvbeXwwDlOXx3DbJGd0Y+7rzAYtKptaqP5e49Hkf0ZuRn/8nAKLKJ3FkLpg6C3JVHPpXYCdWgb7Lm8NM7ImQ5eu0QNYzmKspMxxQxkw7+ilhfrsqJVoBxSNbcYzxZkmNgy2Ag0HzYacw+yxkxymv9sPjjb5Q0P+nDqDyHLyaoVUVXCXF8XjaVq+4S6WyVlAixDNu2j/oZ/1XFhb29RFLC0RE7RkEF8QWDfgvfKM7BdyjIUGgHz01JLmMY2xGcjcHzujhJrsh+mrcRdSEF3tXWmPeADDcYdW/dRhxAZ37LtrSZdp28Rae93JBZEUXbnrBgkgDcr9dxwZeASlS;"
req = req & "JSESSIONID=mQYqJL0LcfzP6c1WJMHN7ncPwBpMJphpJpRS1Jls18Pms3vXzBLx!383278467; synopticBackButton=;"
myURL = "https://authenticationpage.com"
Dim WinHttpReq As Object, oStream As Object
Set WinHttpReq = CreateObject("WINHTTP.WinHTTPRequest.5.1")
WinHttpReq.Open "POST", myURL, False
WinHttpReq.send "USER=USER&PASSWORD=PASSWORD"

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    WinHttpReq.Open "POST", "https://o2.grdf.fr/planningM.do", False
    WinHttpReq.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:32.0) Gecko/20100101 Firefox/32.0"
    WinHttpReq.setRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8"
    WinHttpReq.setRequestHeader "Accept-Language", "fr,fr-fr;q=0.8,en-us;q=0.5,en;q=0.3"
    WinHttpReq.setRequestHeader "Accept-Encoding", "gzip, deflate"
    WinHttpReq.setRequestHeader "DNT", "1"
    WinHttpReq.setRequestHeader "Referer", "https://o2.grdf.fr/planningM.do"
    WinHttpReq.setRequestHeader "Cookie", req
    WinHttpReq.setRequestHeader "Connection", "keep-alive"
    WinHttpReq.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    WinHttpReq.setRequestHeader "Content-Length", "530"
    WinHttpReq.send ("mlEntitySlc=1296&mlSiteSlc=2989&msSelectedDate=23%2F10%2F2014&msType=M&miOptionHour=&miOptionDay=0&msDate=20141023&msModeSearch=submitExport&msComplInfo=&msOptionId=&mbReloadOption=false&mlFormIndspPkPers=&mlFormIndspPkIndispo=&mlFormIndspPkNew=&msFormIndspDate=&msFormIndspHBegin=&msFormIndspHEnd=&msFormIndspComment=&mlFormIndspReport=-1&miNbWeeks=1&mbPkGroup%5B0%5D=on&mlPkGroup%5B0%5D=5239&mbPkGroup%5B1%5D=on&mlPkGroup%5B1%5D=5250&mbPkGroup%5B2%5D=on&mlPkGroup%5B2%5D=5258&mlPkIndispoForRpt=-1&msReportDate=&msReportComments=")
    If WinHttpReq.Status = 200 Then
        Set oStream = CreateObject("ADODB.Stream")
        oStream.Open
        oStream.Type = 1
        oStream.Write WinHttpReq.responseBody
        oStream.SaveToFile "D:\file.xls", 2 ' 1 = no overwrite, 2 = overwrite
        oStream.Close
    End If
End If

End Sub

 

[Kyle123]

Try setting the stream type before opening it

 

[Ooalkman]

Nothing different. Maybe it didn't take the good stream ?
Besides I have another problem that may not have any solution : when the file takes too long to generate I get an error in VBA "The operation timed out". I guess there's nothing I can do ?