Excel Web Query Object and Cookies: Is there a better way?

Question

I have a HTML web page at work that I want to query data from tables into excel 2007. This web page requires I sign on with a password. I sign in with my normal IE7 browser, then I go to DATA -> connections -> my connections and edit the query. This reads the IE7 cookie cache and I re-POST the data to connect to the server's security by clicking "retry" when it says "the web query returned no data". After I do this, the data imports fine.

I can do this just fine and it only needs to be done once a day. Other users of my application find this difficult which leads to my question:

Is there a way to automatically POST this data back with VB? I'm thinking maybe I should use the cookie property of the IE.Document.cookie?

Answer 1

I'm calling the following login script, before I continue with the web query (set reference to XML library). Look around to find some instructions how you can find your POST parameters.

Sub XMLHttpLogin()

    Dim i As Integer
    Dim sExpr As String
    Dim sPar As String, sURL as String
    Dim sResp As String
    Dim XMLHttp As MSXML2.XMLHTTP60

    Set XMLHttp = New MSXML2.XMLHTTP60

    sPar = "name=user1&pass=pass1&form_id=form1" 'The parameters to send.
    sURL = "http://www.stackoverflow.com"

    With XMLHttp
        .Open "POST", sURL, True 'Needs asynchronous connection
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send (sPar)

        i = 0 'wait until data has been downloaded
        Do While i = 0
            If .readyState = 4 Then
                If .Status = 200 Then Exit Do
            End If
            DoEvents
        Loop

        sResp = .responseText 'contains source code of website
        sExpr = "not-logged-in" 'look for this string in source code

        If InStr(1, sResp, sExpr, vbTextCompare) Then
            MsgBox "Not logged in. Error in XMLHttpLogin"
        End If
    End With
End Sub