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
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
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 on
Confused
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 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
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
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
Sad
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 on
Fedup
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 on
Happy
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 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: