It took a while to work this out, but I developed some code that can get the session ID from the web page and then send it in the web query to authenticate the login. The XMLDom approach was not ideal as it is not a given that the web page is valid XML under the XHTML standard. Instead, the following code uses Internet Explorer to recuperate the HTML of the page, extract the session ID from the HTML string and attach it to the web query call.
VBA Code:
Sub Login_WebQuery_SessionID() Dim MyPost As String Dim SessionIDName As String Dim SessionIDValue As String Dim StartPos As Single Dim EndPos As Single Dim tempStr As String
Const MyUrl As String = "http://www.bentekenergy.com/Benport/Login.aspx" 'change URL here Const PostUser As String = "Login1$UserName=User Name" 'Change user name here Const PostPassword As String = "&Login1$Password=password" 'Change password here SessionIDName = "__VIEWSTATE" 'change session ID name here
'Get the HTML of the URL Set IE = CreateObject("InternetExplorer.Application") IE.navigate MyUrl While IE.Busy DoEvents Wend x = IE.document.body.innerHTML IE.Quit
'Narrow down the HTML to extract the Session ID value StartPos = Application.WorksheetFunction.Find(SessionIDName, x) tempStr = Mid(x, StartPos, Len(x) - StartPos) StartPos = Application.WorksheetFunction.Find("value=", tempStr, 1) tempStr = Mid(tempStr, StartPos, Len(tempStr) - StartPos) EndPos = Application.WorksheetFunction.Find(" ", tempStr, 1) tempStr = Mid(tempStr, 7, EndPos - 7) tempStr = Replace(tempStr, """", "") SessionIDValue = tempStr
'Attach the Login and Password MyPost = PostUser & PostPassword
'Attach the required Session ID MyPost = MyPost & "&" & SessionIDName & "=" & SessionIDValue
'Run the Web Query With ActiveSheet.QueryTables.Add(Connection:= _ "URL;" & MyUrl, Destination:=Cells(1, 1)) .PostText = MyPost .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With
End Sub
I have created a new post with this solution here as I think that it is useful on its own.
