VBA and Excel: surfing a site bypassing authentication protected by Siteminder

Question

My goal: entering automatically an intranet site in order to fetch data in .xls files

Preliminary explanation: I regularly fetch data a large number of times in a single day via manual operations; I need an automatic solution to save time. Means avalaible: Excel and Vba (external constraints established by my firm)

The first attempt I’ve done is the following, performing the login operation (protected by Siteminder) and joining the home page:

Sub enter_site()
Dim myId As String, myCode As String
Dim s3 As Worksheet
Dim IE As Object, frm As Object
Set s3 = ThisWorkbook.Worksheets("Sheet3")
myId = s3.Range("I1")      
myCode = s3.Range("K1")   
Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .navigate "https://intranetsite.com/up-password/LoginUp-Password.jsp?TARGET2=https://intranetsite.com/up-password/login/login_redirect.html"
        While .Busy Or .readyState <> 4: DoEvents: Wend             
      'LOGIN     
        Set frm = .document.forms(1)
        frm.Item("USER").Value = myId
        frm.Item("PASSWORD").Value = myCode
Application.Wait (Now + TimeValue("0:00:02"))
        frm.submit        
        While .Busy Or .readyState <> 4: DoEvents: Wend        
    End With

‘CHANNEL TOWARDS HOME PAGE
With IE
    .navigate "https://site2.intranet.com/mask/home.aspx"
    While .Busy Or .readyState <> 4: DoEvents: Wend
End With

It is ok up to a certain point. I mean: I can join the home page but I cannot, despite the different strategies I’ve tried, fetch the needed data because:

1. I cannot manipulate “hostile” calendars and dropdown menus as I usually do in other circumstances;
2. I should also manipulate “hostile” pop-ups along the way.

So, in my opinion, the best solution is about MSXML2.XMLHTTP or WinHttp.WinHttpRequest.5.1 methods, or stuff like that. But unfortunately I’m not familiar with them. Anyway, I’ve tried to move on. I am not one who is easily discouraged and I’ve parsed the operations using Fiddler.

So, I’ve launched a Get request as follows:

Dim URL As String, strResponse As String
Dim objHTTP As Object
URL = https://intranetsite.com/up-password/LoginUp-Password.jsp?TARGET2=https://intranetsite.com/up-password/login/login_redirect.html
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
    With objHTTP
        .Open "GET", URL, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send
        strResponse = .responseText
        Sheets(2).Range("A1") = strResponse
End With

Then, I’ve captured a couple of related cookies exploiting the tips in this page: How to set and get JSESSIONID cookie in VBA? I've put them in 2 cells:

Dim ck1 As String
Dim ck2 As String
        ck1 = Sheets(2).Range("B12")
        ck2 = Sheets(2).Range("B13")

Suppose the following values: ck1 = “JSESSIONID=blablabla” ck2 = “BIGipServerauth-=zzzzzzzzzzzzzzzzzzzzzz”

Furthermore, as specified above, I have:

myId = s3.Range("I1")      '<<< Username (johnsmith)
myCode = s3.Range("K1")   '<<< Password (london55)

Now, the next step is performing the login operation via a post request.

In Fiddler I read:

POST https://intranetsite.com/siteminderagent/forms/login.fcc HTTP/1.1
Accept: text/html, application/xhtml+xml, */*
Referer:  https://intranetsite.com/up-password/LoginUp-Password.jsp?TARGET2=https://intranetsite.com/up-password/login/login_redirect.html _redirect.html
Accept-Language: it-IT
User-Agent: Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)
Content-Type: application/x-www-form-urlencoded
Accept-Encoding: gzip, deflate
Host: intranetsite.com
Content-Length: 181
Connection: Keep-Alive
Cache-Control: no-cache
Cookie: JSESSIONID=…..; BIGipServerauth-……..; _WL_AUTHCOOKIE_JSESSIONID=…; SMSESSION=…

SMENC=UTF-8&target……..login_redirect.html&smauthreason=0&USER=johnsmith&PASSWORD=london55

How should I build the post request?
(I noted I probably need to capture other cookies: _WL_AUTHCOOKIE_JSESSIONID and SMSESSION).
(I don’t know what SMENC exactly is; anyway I can easy built it using variables stored in USER and PASSWORD).