reading password-protected, open webpage

[Kevin]

I monitor the performance of my 401(k) by downloading prices from yahoo.com, using VBA of course. This has become a daily routine. With fund prices and number of shares per fund I can determine the dollar value of each fund (i.e., current balance). Problem is, the number of shares per fund is not something I can download using VBA. This is because the fund manager's website is password-protected

Is it possible to read from an open webpage? If I were to establish the target html in a separate IE window, can I use VBA code to read from it? That is, rather than navigating to the URL and usin
CreateObject("InternetExplorer.Application").Document.Body.innerTex

Thanks

Kevin

 

[Tom Ogilvy]

Can you use GetObject to get the already open instance of IE and then use
document.body.InnerText?

--
Regards,
Tom Ogilvy

I monitor the performance of my 401(k) by downloading prices from

yahoo.com, using VBA of course. This has become a daily routine. With fund
prices and number of shares per fund I can determine the dollar value of
each fund (i.e., current balance). Problem is, the number of shares per
fund is not something I can download using VBA. This is because the fund
manager's website is password-protected.

Is it possible to read from an open webpage? If I were to establish the

target html in a separate IE window, can I use VBA code to read from it?
That is, rather than navigating to the URL and using

 

[Kevin]

Thanks Tom.

In my attempts to execute the following code (NOTE: the webpage I want to read from is already open),

Dim IE As Object
Dim sHTML As String
Set IE = GetObject(, "InternetExplorer.Application")
IE.Visible = True
sHTML = IE.Document.Body.innerText

I get the following error when the program reaches the last line.

Run-time error '-2147467259 (80004005)':
Method 'Document' of object 'IWebBrowser2' failed

Kevin

----- Tom Ogilvy wrote: -----

Can you use GetObject to get the already open instance of IE and then use
document.body.InnerText?

--
Regards,
Tom Ogilvy

I monitor the performance of my 401(k) by downloading prices from

yahoo.com, using VBA of course. This has become a daily routine. With fund
prices and number of shares per fund I can determine the dollar value of
each fund (i.e., current balance). Problem is, the number of shares per
fund is not something I can download using VBA. This is because the fund
manager's website is password-protected.target html in a separate IE window, can I use VBA code to read from it?
That is, rather than navigating to the URL and using

 

[Tom Ogilvy]

In a previous post by Jake Marx, he showed a different syntax for InnerText

Sub Demo()
Dim ie As Object
Dim nFile As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = False
.Silent = True
.Navigate "www.yahoo.com"
Do Until Not .Busy
DoEvents
Loop
nFile = FreeFile
Open "D:\yahoo.txt" For Output Shared As #nFile
Print #nFile, .Document.DocumentElement.InnerText
Close #nFile
.Quit
End With
Set ie = Nothing
End Sub


Using .Document.DocumentElement.InnerText

This did work for me with an open page in IE

Sub Tester1()
Dim IE As Object
Dim sHTML As String
Set IE = GetObject(, "InternetExplorer.Application")
IE.Visible = True
sHTML = IE.Document.Documentelement.innerText
Debug.Print sHTML
Set IE = Nothing
End Sub

 

[Kevin]

Thanks Tom. Your Tester1 code works like a charm

I noticed that Visible = True places the webpage on top of the Excel window
Since I prefer to reman in Excel after executing the code I set Visible = False

Setting Visible = False seems to have two effects. It also closes the webpage

How can keep the webpage open but displayed behind the Excel window

Thanks again

Kevin

----- Tom Ogilvy wrote: ----

In a previous post by Jake Marx, he showed a different syntax for InnerTex

Sub Demo(
Dim ie As Objec
Dim nFile As Intege

Set ie = CreateObject("InternetExplorer.Application"

With i
.Visible = Fals
.Silent = Tru
.Navigate "www.yahoo.com
Do Until Not .Bus
DoEvent
Loo
nFile = FreeFil
Open "D:\yahoo.txt" For Output Shared As #nFil
Print #nFile, .Document.DocumentElement.InnerTex
Close #nFil
.Qui
End Wit
Set ie = Nothin
End Su


Using .Document.DocumentElement.InnerTex

This did work for me with an open page in I

Sub Tester1(
Dim IE As Objec
Dim sHTML As Strin
Set IE = GetObject(, "InternetExplorer.Application"
IE.Visible = Tru
sHTML = IE.Document.Documentelement.innerTex
Debug.Print sHTM
Set IE = Nothin
End Su


-
Regards
Tom Ogilv

 

[Kevin]

Thanks Tom.

Your Tester 1 code works like a charm. But I want to keep the Excel window up front.
So I made several changes.

Sub Tester2()
Dim IE As Object
Dim sHTML As String
On Error Resume Next
Set IE = GetObject(, "InternetExplorer.Application")
sHTML = IE.Document.Documentelement.innerText
IE.Visible = False
If sHTML = "" Then
MsgBox "Webpage was not open!"
Exit Sub
End If
Set IE = Nothing
' use sHTML
End Sub

Thanks again Tom.
----- Tom Ogilvy wrote: -----

In a previous post by Jake Marx, he showed a different syntax for InnerText

Sub Demo()
Dim ie As Object
Dim nFile As Integer

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = False
.Silent = True
.Navigate "www.yahoo.com"
Do Until Not .Busy
DoEvents
Loop
nFile = FreeFile
Open "D:\yahoo.txt" For Output Shared As #nFile
Print #nFile, .Document.DocumentElement.InnerText
Close #nFile
.Quit
End With
Set ie = Nothing
End Sub


Using .Document.DocumentElement.InnerText

This did work for me with an open page in IE

Sub Tester1()
Dim IE As Object
Dim sHTML As String
Set IE = GetObject(, "InternetExplorer.Application")
IE.Visible = True
sHTML = IE.Document.Documentelement.innerText
Debug.Print sHTML
Set IE = Nothing
End Sub

 

[Tom Ogilvy]

try just deleting the IE.Visible line.

Since it is already open and visible, no reason to do anything with it.

In any event (alreayd open or being opened by your code), you should be able
to bring Excel back to the top with

AppActivate Application.Caption

 

[Kevin]

Works great

Thanks again Tom

----- Tom Ogilvy wrote: ----

try just deleting the IE.Visible line

Since it is already open and visible, no reason to do anything with it

In any event (alreayd open or being opened by your code), you should be abl
to bring Excel back to the top wit

AppActivate Application.Captio