Web Query to download web site with user id and password

[nanjing]

I try to get access a web page with userid and password to download the data by using web query and EXCEL Macro.
My userid and password won't be recored in my EXCEL Macro.
How could I do this by using Macro and specify userid and password in my macro and automate my process so I could just run EXCEL Macro to get new data.
Any suggestion is greatly appreciated.

 

[Datsmart]

Welcome to the board!

Sorry there is no quick answer, but Nate Oliver has discussed this in depth here:
http://www.mrexcel.com/board2/viewtopic.php?p=868291#868291

 

[kilo94]

But what about web query?

John et al,
My question is similar. I've searching the forums for a similar question, and it looks like all of the replies deal with using VBA that opens Internet Explorer instead of the web query function. Am I correct in reading this?

I have a fundamental 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!

 

[Datsmart]

kilo94,
The reason you don't see code to use a Web Query is that you can not pass a username and password that way. You have to directly access the internet to get your credentials through. Microsoft used to allow web queries to pass names and passwords, but it was considered a security breach, so they no longer allow it. They have published articles about this in the past.
Web queries are still handy if you don't need to log-in.

 

[kilo94]

OHHHH, that's why I can't find anything. Thanks John. OK, so my next question is can I write some code to the following:

1. Open the web query function
2. Go to investors.com
3. Prompt for a user id which will then populate the user name on the website
4. Prompt for a password and then populate the password form on the website.
5. Then close the web query

Is this possible with VBA?

 

[Datsmart]

The link I posted on Dec 07 shows how Nate Oliver, one of the resident experts on this kind of question, has gotten user id and passwords to a web site. You could alter his code examples with an input message box to prompt for the username and password.

 

[kilo94]

I was looking at the post, and I tried it, but it opens Internet Explorer vs. a Web Query box. Do you know how to make it open up a web query?

 

[Datsmart]

That is what I tried to tell you earlier, you can not use a Web Query if you need a username and password. A web query is a one step, all code quick connection to gather data. You can not pause it during its' running.

If anyone knows of a way around this I would love to hear it. I have a number web queries I would like to have do this too.