PDA

View Full Version : Sleeper: HTML Data to Excel Cells...Help



JShelb
03-19-2008, 11:19 AM
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. :banghead:

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. :think:

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, :rofl: .

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
03-21-2008, 06:40 AM
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
03-22-2008, 04:47 AM
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
03-24-2008, 06:41 AM
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