Get Response Header Using InternetExplorer.Applica

Question

could someone help me or at the very least point me in the right direction.

I am attempting to write an automation script in VBA that will log into a web site and hit some buttons and checkboxes on that site which will then trigger a download.

The webpage is using ASP.net.

I can achieve exactly what i want by using the InternetExplorer.Applicaion object but the problem is with saving the file as i am using APIs and SendKeys to the SaveAs dialog window, it works but is very crude and can easily fail and i really want this to be able to run on its own without any issues.

Ideally i would love to be able to use a HTTP request / response approach but i have been trying for hours without any success due to the sites authentication and i also think it has some protection against scripts as the cookie file has the HttpOnly flag set.

The file i want to download is text/csv and the outputted data can be accessed via the http response header which i can see via Fiddler.

Is there any way to get the HTTP response header from the InternetExplorer.Applicaion object, i have been looking at the MS documentation and I do not see any obvious way to get to this?

Here is the code i am using at the moment:

Private Function GetFile() As Scripting.File

On Error Resume Next

Dim objFso As Scripting.FileSystemObject: Set objFso = New Scripting.FileSystemObject

Dim strFileName As String: strFileName = Year(Now()) & Month(Now()) & Day(Now()) & Hour(Now()) & Minute(Now()) & Second(Now()) & ".csv"
Dim strFolder As String: strFolder = objFso.GetAbsolutePathName(Environ("TEMP")) & "\"

Dim strSaveAsFullPath As String: strSaveAsFullPath = strFolder & strFileName

Dim objIE As InternetExplorer: Set objIE = New InternetExplorer

With objIE

    .Visible = True

    .Navigate2 "WEB URL 1"

    Do Until Not .Busy
        Application.Wait Now + TimeValue("00:00:01")
        DoEvents
    Loop

    .Document.getElementById("ctl02_txtUserId").value = "USER"
    .Document.getElementById("ctl02_txtPassword").value = "PASS"
    .Document.getElementById("ctl02_btnLogon").Click

    Do Until Not .Busy
        Application.Wait Now + TimeValue("00:00:01")
        DoEvents
    Loop

    .Navigate2 "WEB URL 2"

    Do Until Not .Busy
        Application.Wait Now + TimeValue("00:00:01")
        DoEvents
    Loop

    .Document.getElementById("ddlTables").selectedIndex = 8
    .Document.forms(0).submit

    Do Until Not .Busy
        Application.Wait Now + TimeValue("00:00:01")
    Loop

    .Document.getElementById("gvFields_lnkbtnSelectAll").Click

    Do Until Not .Busy
        Application.Wait Now + TimeValue("00:00:01")
        DoEvents
    Loop

    .Document.getElementById("btnRetrieveData").Click

    Do Until Not .Busy
        Application.Wait Now + TimeValue("00:00:01")
        DoEvents
    Loop

    .Document.getElementById("btnRetrieveData").Focus

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'THIS IS WHERE I AM DOWNLOADING THE FILE USING SENDKEYS :( 'I NEED A BETTER SOLUTION PLEASE ??? ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Application.Wait Now + TimeValue("00:00:01")
    DoEvents

    Application.SendKeys "{TAB}", True
    Application.SendKeys "{TAB}", True
    Application.SendKeys "{DOWN}", True
    Application.SendKeys "{DOWN}", True

    Application.SendKeys "a", True

    Application.Wait Now + TimeValue("00:00:01")
    DoEvents

    Application.SendKeys strSaveAsFullPath, True

    Application.SendKeys "{TAB}", True
    Application.SendKeys "{TAB}", True
    Application.SendKeys "{TAB}", True
            Application.Wait Now + TimeValue("00:00:01")
    DoEvents

    Application.SendKeys "s", True
    Application.Wait Now + TimeValue("00:00:02")
    Application.SendKeys "y", True

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    .Quit

End With

Dim objFile As Scripting.File: Set objFile = objFso.GetFile(strSaveAsFullPath)

Set GetTransworldFile = objFile

Set objIE = Nothing
Set objFso = Nothing

End Function

Answer 1

I finally worked out how to do this, i used XMLHTTP and set various headers to masquerade as a legitimate browser, i then pulled the viewstate and the event validation and used that to post the various bits of form data. i am so happy, i spent most of my day working on this :)

i wont post my code as it is in a bit of mess but if anyone else comes across this kind of problem you should download and use Fiddler to capture the HTTP posts and responses from the browser and then using your script try and simulate the browser by setting the same headers and form data, do that and it should be easy..