Sleeper: HTML Data to Excel Cells...Help

[JShelb]

Hello all,

I've been stalking your site for sometime now and usually am able to search enough to find what I need or the boost I need to get me in the right direction. However, I've came to a crossroads and am unable to push past this problem.

A little background, I'm nub in programming overall, but I have most of my knowledge in vb.net. I have some scripting, html, and vba experience, but not a lot. I believe I read the logic and code decent enough to understand, I just have the problem of knowing what methods there are and what a lot of the methods do.

To the issue:
I have an excel spreedsheet that I record daily numbers/tasks on; like a check-off sheet for the day. I've successfully automated a decent amount of it via buttons, file streaming/reading/searching, links, etc. However, the point I am at now is populating a few cells (6) with data gathered from 6 separate websites. My plan was to create a button for each device and OnClick surf to the site, parse the html for the 3 bits of data, and plop the data into the defining cells. So far, I have successfully navigated to the site and well, that's it, .

Code I have:

 
Private Sub CommandButton1_Click()
Dim ie As InternetExplorer
Dim temp As String
Set ie = New InternetExplorer
ie.Navigate ("IP_Address(Private)")
ie.Visible = True
Do Until ie.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
temp = ie.Document.all("deviceInfo.tempF").Value
End Sub

Attached is a sample of the html code. Specifically, I'm looking for the values of: deviceInfo.tempF=74.8 & deviceInfo.humid=36.9
I only want the numbers, nothing else, placed into certain cells.

Thank you in advance for any assistance. I'll try to be as helpful as I can and provide any details I can muster up. The site is on a private intranet, so it will be unviewable. Sorry if this is too long.

Jason

[JShelb]

Did I post this in the wrong place or did I come across wrong? I wasn't expecting exact code to make this work. I was just looking for assistance on how to get this rolling. VBA intellisense is weak compared to VB.net and my intellisense is pathetic.

Bump

[T-J]

First set references to Microsoft Internet Controls and Microsoft HTML Object Library (Tools > References)

In the HTML Document Object Model you need getElementById. Use like this:

Sub GetTemperatureAndHumidity()
    Dim MyIE As InternetExplorer    'Microsoft Internet Controls
    Dim myPage As HTMLDocument      'Microsoft HTML Object Library
    
    Set MyIE = New InternetExplorer
    MyIE.Navigate ("Your_URL_String")
    
    Do:   DoEvents:   Loop While MyIE.Busy Or MyIE.readyState <> READYSTATE_COMPLETE
    
    MyIE.Visible = True
    'Internet security bleep - Javascript - Go to IE Window and click allow blocked content
    Set myPage = MyIE.document
    
    Debug.Print myPage.getElementById("TemperatureGraphActualValue").innerText
    Debug.Print myPage.getElementById("HumidityGraphActualValue").innerText
    
    MyIE.Quit
End Sub

In my case, because of the Javascript, Internet Explorer bleeps and I have to go to the IE window and click Allow blocked content.

[JShelb]

TJ,

Thanks for the response. I can see that I wasn't too terribly far off. I was missing the Internet Controls Reference, I already had the Object Library.

I ran the code as suggested and didn't have the javascript popup like you were experiencing. However, I did have one error occur on this line:

Debug.Print myPage.getElementById("TemperatureGraphActualValue").innerText

Run-time error '91':
Object variable or With block variable not set

I'm pretty sure, but not positive, this occurred because it was unable to locate the ID and grasp the value.

The page is broken into sections (frames maybe?) and this may have to be specified. It has a left margin bar, a bar across the top with tabs, then the section where the html code i posted is located (I should have added this to my OP). I can right-click > view source on each of these sections and receive different html code.

So, to retrieve these values, would I need something like this?:

MyPage.frames(2).all.getElementByID("TemperatureGraphActualValue").innerText

Thanks again,
Jason