The Code: Automating a web query login to download data Excel help response (21 Nov 2009)

When I compile the VBA project, it seems to do this without problems. But when I press run, it gives me that error I mentioned before. Is this the right way to debug? I'm not sure how to "hover" over anything in the code while doing this - nothing shows up...This is the code I have:
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/DisplayReportNews.aspx?LOC=1&ID=0&doc=BENTEK_EMF_NukeOutages.pdf" 'change URL here
Const PostUser As String = "Login1$UserName=XXXXX" 'Change user name here
Const PostPassword As String = "Login1$Password=XXXXX" 'Change password here
SessionIDName = "XXXXX" 'change session ID name here
'tes
'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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

The code

Excel Help for The Code in Automating Data Analysis Tasks

The Code

Excel templates and solutions matched for the code:

	When I compile the VBA project, it seems to do this without problems. But when I press run, it gives me that error I mentioned before. Is this the right way to debug? I'm not sure how to "hover" over anything in the code while doing this - nothing shows up...This is the code I have: 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/DisplayReportNews.aspx?LOC=1&ID=0&doc=BENTEK_EMF_NukeOutages.pdf" 'change URL here Const PostUser As String = "Login1$UserName=XXXXX" 'Change user name here Const PostPassword As String = "Login1$Password=XXXXX" 'Change password here SessionIDName = "XXXXX" 'change session ID name here 'tes '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 Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub
	Posted by vzny254 on 21 Nov 2009