web query w/login and password
Fun Kid
basis and I intend to use an excel web query to do that. But the
website requires a user id and a password in order to access the
information. How can I do this?
Eventually, I would like to set up a macro using VBA for excel so that
each time I open the file, it will provide the website with the user id
and login, extract the data and plot the daily balance.
Any suggestions would be appreciated.
Tom Ogilvy
understand it, but some sites support
http://userid:pass...@my401ksite.com
--
Regards,
Tom Ogilvy
"Fun Kid" <funk...@hotpop.com> wrote in message
news:%23RfprBG...@TK2MSFTNGP12.phx.gbl...
Jake Marx
What is the URL for the login page? Is it a popup login dialog, or do the
textboxes reside on the web page itself?
There are ways to do this, but they aren't terribly straightforward.
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
Fun Kid
information is contained in the web page.
I'm happy to know that there is a way to do this. Thnx.
"Jake Marx" <msn...@longhead.com> wrote in message
news:#fpdpWGL...@tk2msftngp13.phx.gbl...
Fun Kid
I am trying to work with.
"Tom Ogilvy" <twog...@msn.com> wrote in message
news:ezClgJGL...@TK2MSFTNGP10.phx.gbl...
Jake Marx
Please provide the URL (address) for the web page, and we may be able to
help out further. The process really depends on how the page is set up.
Fun Kid
"Jake Marx" <msn...@longhead.com> wrote in message
news:#tk4XKIL...@TK2MSFTNGP11.phx.gbl...
Jake Marx
OK - I can get you logged in, but I'm not sure how you're going to proceed
from there. If you are taken directly to the page that you can pull data
from, then you should be able to parse out sResponse to get what you need.
If you are still a mouse click or two away, it becomes more difficult.
Anyway, the following code will get the HTML source for the page that is
displayed after you log in to the site. You must set a reference to
"Microsoft XML 4.0" via Tools | References in order to run the code. Modify
the SSN and PIN in the string to your SSN and PIN. To make it a bit safer
(so your SSN is not stored in the workbook), you may want to take in the SSN
and PIN via a UserForm or InputBoxes.
Public Sub test()
Dim xml As XMLHTTP40
Dim abytPostData() As Byte
Dim sMode As String
Dim sResponse As String
Dim nStartPos As Integer
Dim nEndPos As Integer
abytPostData = StrConv("SSN=111223333&PIN=1234&ml=https://" & _
"account.gwrs.com/Central/Login/FCLoginRedirector.Asp" & _
"menu_param=/tl001/menu/frameset.asp&AUTHORIZING=true&" & _
"bypass_oe=false", vbFromUnicode)
Set xml = New XMLHTTP40
With xml
.Open "POST", _
"https://www.fascorp.com/servlet/AccountAccess/" & _
"Gwrs/individual_info"
.setRequestHeader "Content-Type", _
"application/x-www-form-urlencoded"
.send abytPostData
sResponse = .responseText
End With
Debug.Print sResponse
Set xml = Nothing
End Sub
ron
the same goal within Excel. An alternative method to that proposed by
Jake follows:
Sub 401K()
' Prepare to open the web page
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.Navigate "http://www.gwrs.com/"
' Loop until the page is fully loaded
Do Until Not .Busy
DoEvents
Loop
' Make the desired selections on the web page and click the submit
button
Set ipf = ie.document.all.Item("SSN")
ipf.Value = "123456789"
Set ipf = ie.document.all.Item("PIN")
ipf.Value = "abc123"
Set ipf = ie.document.all.Item("btnarrow")
ipf.Value = "submit"
ipf.Click
' Loop until the page is fully loaded
Do Until Not .Busy
DoEvents
Loop
End With
' Select and copy all of the data from the web page
ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
' Close the internet explorer application
ie.Quit
' Now write code to paste the web page into a worksheet and begin to
' process / extract the information of interest
End Sub
You can usually find the "item names" by viewing the source code for
the web page (right click on the web page and "View Source" is an
option). I can't tell for sure if the above code is error free,
because I don't have a valid PIN. I do know that the above code
correctly enters the SSN and PIN into the login windows and it takes
me to a page that asks for a valid SSN and PIN. So if it doesn't
work, I think it is close...Ron
Fun Kid
4.0". Could you be kind enough to provide me with the step by step
instructions to do so. Thanks much.
"Jake Marx" <msn...@longhead.com> wrote in message
news:uBA2gnJL...@TK2MSFTNGP12.phx.gbl...
Jake Marx
> I am not sure what you mean by setting a reference to "Microsoft XML
> 4.0". Could you be kind enough to provide me with the step by step
> instructions to do so. Thanks much.
Go into the VBE (Alt+F11 from Excel). Select Tools | References. Scroll
down until you find "Microsoft XML, v4.0" and check the box next to it.
Click OK. Now your VBA Project has a reference to the XML library and can
use it in code.
Fun Kid
While trying your method I got an error message.
Run-time error '-2147221248 (80040100)':
Method 'ExecWB' of object 'IWebBrowser2' failed
Also in the previous discussion thread, Jake said I should set me
references to "Microsoft XML, v4.0". I dont have the option to select
v4.0 but I do have v3.0. Even after clicking on that box I got an
error.
Thanks in advance.
"ron" <oit...@yahoo.com> wrote in message
news:1807c7b7.04042...@posting.google.com...
Jake Marx
> Also in the previous discussion thread, Jake said I should set me
> references to "Microsoft XML, v4.0". I dont have the option to select
> v4.0 but I do have v3.0. Even after clicking on that box I got an
> error.
Ron's code shouldn't require any references. My code did because it
utilized the XML library and was early bound, whereas Ron's code is late
bound.
I think Ron just forgot to give you the literal values for the OLECMD
contstants. Here they are:
?OLECMDID_SELECTALL
17
?OLECMDEXECOPT_DONTPROMPTUSER
2
?OLECMDID_COPY
12
?OLECMDEXECOPT_DODEFAULT
0
So, replace OLECMDID_SELECTALL with 17, OLECMDEXECOPT_DONTPROMPTUSER with 2,
and so on.
Fun Kid
I'm definitely getting closer to what I want to do. How do I paste the
information to an excel file? I think I can parse and process the data
after it is dumped in an excel file.
Thanks to both Ron and Jake. You guys have been of great help. Can you
guys recommend names of books or websites where I can read up on this
and get more information. Not only do I hate to post every little
question to the newsgroup but I would also like to gain a good hold of
the basics of manipulating web pages from excel or word.
"Jake Marx" <msn...@longhead.com> wrote in message
news:urqAPEW...@TK2MSFTNGP10.phx.gbl...
Jake Marx
> Thanks Jake, that helped get rid of the error message.
Excellent.
> I'm definitely getting closer to what I want to do. How do I paste
> the information to an excel file? I think I can parse and process
> the data after it is dumped in an excel file.
I'm not sure in Ron's case. I think a simple ActiveSheet.Paste will do the
trick, as his routine copied the webpage to the clipboard.
My example is a bit different, as it retrieves the *source* of the web page
as a string, which you can then parse using InStr, Mid$, etc.
> Thanks to both Ron and Jake. You guys have been of great help. Can
> you guys recommend names of books or websites where I can read up on
> this and get more information. Not only do I hate to post every
> little question to the newsgroup but I would also like to gain a good
> hold of the basics of manipulating web pages from excel or word.
I don't know of any books that deal with this issue in particular. MSDN and
Google (both web and groups) searches are my best friends when it comes to
researching this type of stuff. I would start with a search of Google on
"XMLHTTP" or "InternetExplorer" and go from there. Searching previous
newsgroup posts will often give you excellent results
(http://groups.google.com/).
R.VENKATARAMAN
computer (e.g.yahoo). can you use that facillity?
Tom Ogilvy <twog...@msn.com> wrote in message
news:ezClgJGL...@TK2MSFTNGP10.phx.gbl...