S
Scott Cooper
I wanted some code to log onto my 401k website and extract the values
from various funds in my account. I have been able to combine code
from the following posts to accomplish what I wanted:
http://groups.google.com/group/micr...read/thread/ac1d12f1b716f099/e61e94cde558c098
http://groups.google.com/group/micr...427696373d6/2df958f9930907cd#2df958f9930907cd
Many thanks to those who contributed. Here is the code that I was able
to put together to accomplish what I wanted (to shorten, I've only
include 1 fund as an example):
Private Const myFund1 As String = "Name of my first fund"
Sub loginLM401k()
' Prepare to open the web page
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = False
.Navigate "https://my401ksite.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("USERID")
ipf.Value = "myUserID"
Set ipf = ie.Document.all.Item("PIN")
ipf.Value = "1234"
Set ipf = ie.Document.all.Item("but_login")
ipf.Click
' Loop until the page is fully loaded
Do Until Not .Busy
DoEvents
Loop
' Get page source
s = ie.Document.body.innertext
' Logout, loop until the page is fully loaded, and close ie
Set lo = ie.Document.all.Item("glob_logout")
lo.Click
Do Until Not .Busy
DoEvents
Loop
ie.Quit
End With
' Paste the data into active cell
'/ get Investment Co of America Data
nStart = InStr(1, s, myFund1, vbTextCompare)
If nStart Then
nStart = nStart + Len(myFund1)
nEnd = InStr(nStart, s, vbCrLf)
'Debug.Print myFund1 & ": " & Mid$(s, nStart, nEnd -
nStart)
' put the same thing in the active cell
ActiveCell.Value = myFund1 & ": " & Mid$(s, nStart, nEnd -
nStart)
' move to an empty cell to put the next piece of data into
ActiveCell.Offset(1, 0).Activate
End If
' Close the internet explorer application and do cleanup
Set ie = Nothing
Set ipf = Nothing
Set lo = Nothing
End Sub
Now, instead of defining the name of the funds in the code, I would
like to look them up in a worksheet. Actually, I would like to lookup
all the funds listed in, say, column A, then loop through to get data
for each one. I don't have a clue how to do this. Can anyone help?
Also, is there anything in the code that is unecessary or inefficient?
I'm a novice.
from various funds in my account. I have been able to combine code
from the following posts to accomplish what I wanted:
http://groups.google.com/group/micr...read/thread/ac1d12f1b716f099/e61e94cde558c098
http://groups.google.com/group/micr...427696373d6/2df958f9930907cd#2df958f9930907cd
Many thanks to those who contributed. Here is the code that I was able
to put together to accomplish what I wanted (to shorten, I've only
include 1 fund as an example):
Private Const myFund1 As String = "Name of my first fund"
Sub loginLM401k()
' Prepare to open the web page
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = False
.Navigate "https://my401ksite.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("USERID")
ipf.Value = "myUserID"
Set ipf = ie.Document.all.Item("PIN")
ipf.Value = "1234"
Set ipf = ie.Document.all.Item("but_login")
ipf.Click
' Loop until the page is fully loaded
Do Until Not .Busy
DoEvents
Loop
' Get page source
s = ie.Document.body.innertext
' Logout, loop until the page is fully loaded, and close ie
Set lo = ie.Document.all.Item("glob_logout")
lo.Click
Do Until Not .Busy
DoEvents
Loop
ie.Quit
End With
' Paste the data into active cell
'/ get Investment Co of America Data
nStart = InStr(1, s, myFund1, vbTextCompare)
If nStart Then
nStart = nStart + Len(myFund1)
nEnd = InStr(nStart, s, vbCrLf)
'Debug.Print myFund1 & ": " & Mid$(s, nStart, nEnd -
nStart)
' put the same thing in the active cell
ActiveCell.Value = myFund1 & ": " & Mid$(s, nStart, nEnd -
nStart)
' move to an empty cell to put the next piece of data into
ActiveCell.Offset(1, 0).Activate
End If
' Close the internet explorer application and do cleanup
Set ie = Nothing
Set ipf = Nothing
Set lo = Nothing
End Sub
Now, instead of defining the name of the funds in the code, I would
like to look them up in a worksheet. Actually, I would like to lookup
all the funds listed in, say, column A, then loop through to get data
for each one. I don't have a clue how to do this. Can anyone help?
Also, is there anything in the code that is unecessary or inefficient?
I'm a novice.