Home > Forum Home > Automating Data Analysis Tasks > Automating a web query login to download data Share

Automating a web query login to download data

Excel Help for Automating A Web Query Login To Download Data in Automating Data Analysis Tasks


Forum TopicPost Reply Login

Automating A Web Query Login To Download Data

Rate this:
(2.8/5 from 16 votes)
Confused I have a funamental earnings and valuation model spreadsheet that downloads stock data from a number of different websites via certain add-ins.  One of the sites that it gets information from is investors.com, which requires a login before downloading stock information (not just price/volume/etc data, I get that easily from Yahoo).  Problem is that you have to manually login through a web query each time, and this is the current instructions:
  1. Go to "Data" on the Menu bar, then "Import External Data"
  2. Then go to "New Web Query" and a web browser will open
  3. Navigate to www.investors.com and login clicking remember me
  4. Then exit from the query, now you can use this tab
My question is how can I automate the login via either a macro or VBA code to login based upon a user name in one cell (C1), and a password in another cell (C2)?

Thanks in advance for any help you can provide! 
 Posted by on
 
Replies - Displaying 21 to 30 of 40Order Replies By: Most Recent | Chronological | Highest Rated
Angry
StartPos value
Rate this:
(3/5 from 1 vote)
If you hover your mouse over StartPos when clicking Debug, what value does it give?

The code I posted works for me, except I do not have valid login details to send and I am not using the long URL with the query parameters in it.

If you could post your code (obviously without the login details), that might help.

 Excel Business Forums Administrator
 Posted by on
Confused
the code
Rate this:
(3/5 from 1 vote)
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 on
Confused
macros in excel to login to website
Rate this:
(2/5 from 3 votes)
Hi, I am new to creating macros and I want to create a macro that automatically log in the website and take the user to the desired page.
For Example, Suppose I want to login to [http://www.yahoomail.com/] The functionality of macro should be such that it prompts the user for the username and password and passes the same to the login page of the website and if the credentials are valid then it logs in the user to the website.

Please let me know if this can be done and suggest a wayout if possible.
 leena
 Posted by on
Confused
Have same problem with .post
Rate this:
(3/5 from 1 vote)
Please take a look at this website. I tried all sorts of way but couldn't get it to work.

[http://wwwa1.settrade.com/login.jsp?txtBrokerId=001]

username and password name are
txtLogin and txtPassword.

Thanks a bunch
Palm
 Posted by on
Angry
Method will not work in all cases
Rate this:
(3/5 from 1 vote)
The .Post method for automating web query login will not work in all cases. In the last post, we can see from the page source that the form is posted to an encypted page:

action='https://wwwa2.settrade.com/LoginRepOnRole.jsp'

This should be the target of the web query; however it will not work as the page is secured (https).

Another setback we have seen is the presence of a session variable required by the target page to authenticate the user.  We have provided a solution for this whereby the session variavble value is first obtained then sent back with the login credentials to automate the login.  This is not fully stable and some more thought is required to come up with a robust solution to this issue.

 Excel Business Forums Administrator
 Posted by on
Confused
That's why
Rate this:
(3/5 from 1 vote)
Ah, that's why no matter how i did it, i couldn't get it to work. Thank you for looking into it for me.
Palm
 Posted by on
Confused
server query
Rate this:
(3/5 from 1 vote)
Mmm, Not sure if this is a genuine web query question... 
I've got this excel file that sends a query to an IBM iSeries. I want to automate it but it asks for an authentication... Is there a way that i can put my username and password inside this macro so it logs in automatically?

 VBA Code:
Sub UpdBtn_Update()
    'Excel 2000/XP
    Dim StarQuery As Object
    On Error GoTo Excel97_flag
    Set StarQuery = Application.COMAddIns("SymtraxStarQuery.ExcelAddin").Object
    On Error GoTo 0
    StarQuery.RunTable "\\iseries\STARQUERY\Queries\***.sqx"

    Exit Sub
Excel97_flag:
    'Excel 97
    Application.Run "SQRUNTABLE", "\\iseries\STARQUERY\Queries\***.sqx"
End Sub

 Posted by on
Oops
Pass Variables to COM Add-in
Rate this:
(3/5 from 1 vote)
In the first instance, you should check what methods the COM Add-in supports. If there is a method to process login credentials then you should use this.
An alternative (though not very robust) is to use the SendKeys method, whereby you attempt to apply focus to the running application and then use the SendKeys command within VBA to mimic the login.  

 Excel Business Forums Administrator
 Posted by on
Confused
Problem with SendKeys
Rate this:
(3/5 from 1 vote)
I really have no idea how those COM Add-ins work... So i'm going for the SendKeys option... Problem is that when i use that like this:
 StarQuery.RunTable "\\iseries\STARQUERY\Queries\***.sqx"
SendKeys "username"
it types the username in a cell of my excel sheet and not in the pop-up. I've tried delaying it and putting the code in different order but it does not work.. How do I get it to type my sendkeys info in the pop up?
 Posted by on
Surprised
Focus on Application
Rate this:
(3/5 from 1 vote)
Before you use the SendKeys command you need to set the system to focus on the launched application.
VBA Code:
Shell ("C:\???\appilication_name.exe"), vbMaximizedFocus

 Excel Business Forums Administrator
 Posted by on
 Displaying page 3 of 5 

Find relevant Excel templates and add-ins for Automating a web query login to download data in the

© Business Spreadsheets   Legal   Follow us: