Automating a web query login to download data

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

Automating A Web Query Login To Download Data

Rate this: (2.8/5 from 16 votes)

	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: Go to "Data" on the Menu bar, then "Import External Data" Then go to "New Web Query" and a web browser will open Navigate to www.investors.com and login clicking remember me 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 kilo94 on 18 Dec 2006

Replies - Displaying 11 to 20 of 40

Order Replies By: Most recent | Chronological | Highest Rated

	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 Excel Helper on 27 Jan 2010

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 BillyOcean on 27 Jan 2010

	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 Excel Helper on 27 Jan 2010

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 BillyOcean on 27 Jan 2010

	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 palm on 26 Nov 2009

	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 Excel Helper on 26 Nov 2009

	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 palm on 26 Nov 2009

	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 harleen on 26 Nov 2009

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 vzny254 on 21 Nov 2009

	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 Excel Helper on 20 Nov 2009

Back

Displaying page 2 of 5

Next