web query w/login and password
 

I would like to extract information from a my 401k website on a regular
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.

web query w/login and password
 

There isn't a standard for supplying that information in a URL as I
understand it, but some sites support



--
Regards,
Tom Ogilvy

"Fun Kid" wrote in message
...
I would like to extract information from a my 401k website on a regular
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.

web query w/login and password
 

Hi Fun Kid,

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 wrote:
I would like to extract information from a my 401k website on a
regular 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.

web query w/login and password
 

No, it is not a popup login dialog. The boxes for login and password
information is contained in the web page.

I'm happy to know that there is a way to do this. Thnx.

"Jake Marx" wrote in message
...
Hi Fun Kid,

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 wrote:
I would like to extract information from a my 401k website on a
regular 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.

web query w/login and password
 

web query w/login and password
 

Hi,

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.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Fun Kid wrote:
No, it is not a popup login dialog. The boxes for login and password
information is contained in the web page.

I'm happy to know that there is a way to do this. Thnx.

"Jake Marx" wrote in message
...
Hi Fun Kid,

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 wrote:
I would like to extract information from a my 401k website on a
regular 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.

web query w/login and password
 

http://www.gwrs.com/. Thanks for offering to help.

"Jake Marx" wrote in message
...
Hi,

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.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Fun Kid wrote:
No, it is not a popup login dialog. The boxes for login and
password
information is contained in the web page.

I'm happy to know that there is a way to do this. Thnx.

"Jake Marx" wrote in message
...
Hi Fun Kid,

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 wrote:
I would like to extract information from a my 401k website on a
regular 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.

web query w/login and password
 

Hi,

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

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Fun Kid wrote:
http://www.gwrs.com/. Thanks for offering to help.

"Jake Marx" wrote in message
...
Hi,

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.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Fun Kid wrote:
No, it is not a popup login dialog. The boxes for login and
password information is contained in the web page.

I'm happy to know that there is a way to do this. Thnx.

"Jake Marx" wrote in message
...
Hi Fun Kid,

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 wrote:
I would like to extract information from a my 401k website on a
regular 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.

web query w/login and password
 

It seems that there are usually several different ways to accomplish
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

web query w/login and password
 

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.

"Jake Marx" wrote in message
...
Hi,

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

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Fun Kid wrote:
http://www.gwrs.com/. Thanks for offering to help.

"Jake Marx" wrote in message
...
Hi,

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.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Fun Kid wrote:
No, it is not a popup login dialog. The boxes for login and
password information is contained in the web page.

I'm happy to know that there is a way to do this. Thnx.

"Jake Marx" wrote in message
...
Hi Fun Kid,

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 wrote:
I would like to extract information from a my 401k website on a
regular 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.

web query w/login and password
 

Hi Fun Kid,

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.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

web query w/login and password
 

Dear Ron,

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" wrote in message
om...
It seems that there are usually several different ways to accomplish
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

web query w/login and password
 

Fun Kid wrote:
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 a

?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.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

web query w/login and password
 

Thanks Jake, that helped get rid of the error message.

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" wrote in message
...
Fun Kid wrote:
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 a

?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.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

web query w/login and password
 

Fun Kid wrote:
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/).

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

web query w/login and password
 

some webpages have facility to remember the userid and password in your
computer (e.g.yahoo). can you use that facillity?


Tom Ogilvy wrote in message
...
There isn't a standard for supplying that information in a URL as I
understand it, but some sites support



--
Regards,
Tom Ogilvy

"Fun Kid" wrote in message
...
I would like to extract information from a my 401k website on a regular
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.