Excel Help for Web Query With Session Id Variable

Following a previous post for automating a login to download data with a web query with VBA here, the issue was raised where the web page in question requires a session ID variable to accompany the login details. This scenario is common with .NET ASPX pages in order to authenticate the user.

A solution is to first recuperate the HTML of the web page to post the variables to, extract the session ID value using string manipulation and then attach the session ID value to the web query parameters.

To get the form names required for the code, it is necessary to visit the web page in question and view the source HTML. The form tags begin with "<input.." and it is the "name" attribute that needs to be used.

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.urlhere..." 'change URL here
Const PostUser As String = "LoginFormName=User Name" 'Change form name and user name here
Const PostPassword As String = "&PasswordFormName=password" 'Change form name and password here
SessionIDName = "__VIEWSTATE" 'change session ID form 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 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

Note that this code relies on Internet Explorer. To use another browser, the Shell command can be used to replace the code CreateObject("InternetExplorer.Application").

Web Query with Session ID Variable

Excel Help for Web Query With Session Id Variable in Automating Data Analysis Tasks

Web Query With Session Id Variable

	Success in Debug but... Rate this: (3/5 from 1 vote) Hi, I open my Excel-spreadsheet and paste in the VBA-code. When I run the macro and debug it is a success. But I only receive the 3 text strings from the login-page in the spreadsheet. ( "Username", "Password" and "Remember my login" ) I don't get the actual data from the MyUrl (the webpage behind the login) Please help? //Mads...
	Best Regards
	Posted by madsing on 04 Jan 2011