VBA help to get live data from password protected site

[jbinnerz123]

Hello I am a noob but want to learn and make it easy to display data on my spreadsheet. I'm trying to get a realtime number onto my excel spreadsheet from a website that is password protected. I have the code to open the correct webpage the number is on, but then how do I get the number to show on my spreadsheet in real time. I will post the code I use to open the site, but I do not know where to go from there to get the number.

Inspecting the number I want shows :
<td align="right" class="AccountSummary" style="color: green;>-£5,79</td>

with the -£5.79 the number i want in my spreadsheet realtime. Thank you


Sub login()


Dim i As SHDocVw.InternetExplorer
Set i = New InternetExplorer
i.Visible = True


i.navigate ("website")


Do While i.readyState <> READYSTATE_COMPLETE


Dim idox As MSHTML.HTMLDocument
Set Idoc = i.document


Idoc.all.Pass.Value = "mypassword"

Dim ele As MSHTML.IHTMLElement

Dim eles As MSHTML.IHTMLElementCollection
Set eles = Idoc.getElementsByTagName("button")


For Each ele In eles

If ele.ID = "sgnBt" Then ele.click

Next

end sub

 

[Saurabhj]

Hi, Welcome to Mr. Excel board.

Once you logged-in in the site, you can access required value using below:

msgbox idox.getElementsByClassName("AccountSummary")(0).innerText

 

[jbinnerz123]

Hello Thank you for your reply.

So how would I write that code into my VBA? and then how would I get the value to show up in my excel spreadsheet?

Sub login()


Dim i As SHDocVw.InternetExplorer
Set i = New InternetExplorer
i.Visible = True


i.navigate ("website")


Do While i.readyState <> READYSTATE_COMPLETE

Loop


Dim idox As MSHTML.HTMLDocument
Set Idoc = i.document


Idoc.all.Pass.Value = "password"

Dim ele As MSHTML.IHTMLElement

Dim eles As MSHTML.IHTMLElementCollection
Set eles = Idoc.getElementsByTagName("button")


For Each ele In eles

If ele.ID = "sgnBt" Then ele.Click

Next ele

MsgBox idox.getElementsByClassName("AccountSummary")(0).innerText



End Sub


thank you

 

[jbinnerz123]

Hi, Welcome to Mr. Excel board.

Once you logged-in in the site, you can access required value using below:

msgbox idox.getElementsByClassName("AccountSummary")(0).innerText
[/C
[/QUOTE]
thank you for the reply

How would I write this into the vba? would i have to do another sub function, or do i even have the initial sub login correct if i am just wanting to get the live data? How would i then get the value to show up in the spreadsheet in live time? thank you

 

[Saurabhj]

Hi,

Yes the code is correct. Is this showing the value in message box ?
To show value in sheet use reference of worksheet.

IF not, Do these modifications -

1. After pressing button, Stop VBA for few seconds so that page loads completely.

For Each ele In eles
           If ele.ID = "sgnBt" Then ele.Click
Next ele

Application.Wait (timevalue("00:00:05"))

Worksheets("sheetname").Range("A2") = idox.getElementsByClassName("AccountSummary")(0).innerText