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 21 to 30 of 40

Order Replies By: Most Recent | Chronological | Highest Rated

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

	Old Code Rate this: (3/5 from 1 vote) I tried using the old code, but that does not work for me. It shows me an error when I run it, it says: Websit "http:......... cannot be opened and data cannot be downloaded" What do you think might be wrong?  Thanks for your help
	Posted by Nick1234 on 13 Nov 2009

	Need Help in same case Rate this: (3/5 from 1 vote) I am facing the same problem with the website https://my.secure.home1.ul.com/portal/page/portal/usa/Tools/MyProjects/Open%20Projects Have no clue what I need to do . I need to import data from this website, but the first step is how can I log in with excel. Please help.
	Posted by Nick1234 on 12 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

	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

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

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

	Typo3 Login Error Rate this: (3/5 from 1 vote) Does anybody have an idea how to login into a typo3 backend using excel? (like in 'savecall .de/typo3/backend') It uses these strange variables userident and challenge... I get the typo3 error: Error: This host address ("www.mypage.de") and the referer host ("") mismatches!
	Posted by AndemWue on 15 Oct 2010

	set referer for web query Rate this: (3/5 from 1 vote) Now I think the variables userident and challenge aren't a problem any more. However, I need somehow to spoof the referer to login. I couldn't find a way to do that in a web query. Does anyone have an idea, please?
	Posted by AndemWue on 16 Oct 2010

No code solution to get any data from any website into excel and back Rate this: (3/5 from 1 vote) I have been having some of the same issues of trying to get unstructured data from web pages that require login and transactional navigation. Here is a free tool that i created that allows you to mark the data that you are inteterested on any web page and send it to excel. It also allows you to take any cell values from your spreadsheet and post them on any web service I am currently looking for excel users to help me try the Beta version and give feedback. If interested please email [email protected] Yoav

Posted by Yoavdembak on 07 Dec 2010

Back

Displaying page 3 of 5

Next