Problem reading HTML code from Excel-VBA

[Erik Klausen]

Hi Group.

I'm trying to read a HTML page from VBA. The page contains a graph,
generated as a PNG-file, and I want to insert this graph in the
spreadsheet.

I can read the HTML page using the following code:

Dim ObjIE As New InternetExplorer

ObjIE.Navigate Worksheets("Mellemregninger").Range("B13")
ObjIE.Visible = True
Do Until ObjIE.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop

S = ObjIE.Document.Body.InnerHTML

The HTML code that the program recieves has been parsed by Internet
Explorer, though. If I go to the browser window and select "view
source", I get the original code that I really want, but the VBA code
receives a slightly different version.

This is a problem, since the web page sends some code to handle .png
files. Therefore, the reference to the .png-file has been changed to a
reference to blank.gif when VBA sees it.

Can I do something about it? I'd prefer to solve the problem in the code,
not by configuring the browser. Also, the chance of getting the web page
changed isn't good.

Versions: Excel is version 2003 (11.8105.8107), VBE is version 6.3,
Internet Explorer is version 6.0.2900.2180

TIA,
Erik Klausen

 

[Michel Pierron]

Hi Erik,
Try:

With CreateObject("Microsoft.XMLHTTP")
..Open "GET", Worksheets("Mellemregninger").Range("B13"), False
..Send
S = .ResponseText
End With

MP

 

[Tim Williams]

Exactly what do you mean by "the web page sends some code to handle .png files" ?

Can you share the URL ?


Alternatively you might get something from this code:
'***************************
Option Explicit

Sub tester()
GetFiles "http://www.yahoo.com", "jpg"
End Sub

Sub GetFiles(sURL As String, sExtension As String)

Dim oDoc As New MSHTML.HTMLDocument
Dim iCount As Integer
Dim IE As New InternetExplorer
Dim i As Integer
IE.Visible = True
IE.navigate sURL

Do While IE.ReadyState <> READYSTATE_COMPLETE
Loop

Set oDoc = IE.Document
iCount = 0
For i = 1 To oDoc.images.Length
If oDoc.images(i - 1).href Like "*" & sExtension Then
iCount = iCount + 1
Debug.Print oDoc.images(i - 1).src
saveFile oDoc.images(i - 1).src, _
ThisWorkbook.Path & "\web_images\img_" & _
Right("000" & iCount, 4) & "." & sExtension
End If

If iCount > 3 Then Exit For 'comment out after testing
Next i

Set oDoc = Nothing
Set IE = Nothing
Application.StatusBar = False
End Sub

Sub saveFile(sURL As String, sPath As String)
Dim oXHTTP As New MSXML2.XMLHTTP
Dim oStream As New ADODB.Stream
Dim oFSO As New Scripting.FileSystemObject

oXHTTP.Open "GET", sURL, False
oXHTTP.send

Debug.Print oXHTTP.getAllResponseHeaders



oStream.Type = adTypeBinary
oStream.Open
oStream.Write oXHTTP.responseBody
oStream.SaveToFile sPath, adSaveCreateOverWrite
oStream.Close

Set oXHTTP = Nothing
Set oStream = Nothing
Set oFSO = Nothing

End Sub
'*******************************


Tim

 

[Erik Klausen]

Hi Erik,
Try:

With CreateObject("Microsoft.XMLHTTP")
.Open "GET", Worksheets("Mellemregninger").Range("B13"), False
.Send
S = .ResponseText
End With

Hi Michel

This worked fine Thanks!

KR,
Erik

 

[Erik Klausen]

"Tim Williams" <timjwilliams at gmail dot com> wrote in

Hi Tim.

Exactly what do you mean by "the web page sends some code to handle
.png files" ?

Appearently, previous versions of IE couldn't handle the transparency
that .png files support. Therefore, this little code snip is included in
many HTML pages that use .png graphics:

img { behavior: url("pngbehavior.htc"); }

The file pngbehavior.htc contains code that places a .gif image over the
..png image. This means that when I right-click the image I can save the
..gif-file, and the code I refer to only contains reference to the gif.

Can you share the URL ?

No, it is an internal server.

Alternatively you might get something from this code:

Thanks. For the time being, I'll go with Michels simple code. But I'll
study your code further; I'd like to extract all facilities when I've got
version 1.0 of my program running.

But thanks for your efforts.

KR
Erik