Solved

read HTML from WEB

Posted on 1997-12-20
11
361 Views
Last Modified: 2013-11-13
How can I read in Excel or VBA a HTML page from an URL addres into a File for later use?
0
Comment
Question by:thoba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 8

Expert Comment

by:mrmick
ID: 1446246
If you have the internet transfer control that ships with VB5.  If not I can provide code that would do it with a winsock control.  Do you have access to either of these?
0
 

Author Comment

by:thoba
ID: 1446247
Thank You
I have access to both winsock control and internet transfer control.
Do You have code for using it in VBA with excel ?
Thomas
0
 
LVL 4

Accepted Solution

by:
rantanen earned 100 total points
ID: 1446248
Unfortunately I don't have here Excel installed so I can't really test this with it, but following should work (maybe after some small modifications)

Make following declarations

Const icHostResolvingHost = 1 'The control is looking up the IP address of the specified host computer.
Const icHostResolved = 2   'The control successfully found the IP address of the specified host computer.
Const icConnecting = 3     ' control is connecting to the host computer.
Const icConnected = 4  'The control successfully connected to the host computer.
Const icRequesting = 5     'The control is sending a request to the host computer.
Const icRequestSent = 6    'The control successfully sent the request.
Const icReceivingResponse = 7  'The control is receiving a response from the host computer.
Const icResponseReceived = 8   'The control successfully received a response from the host computer.
Const icDisconnecting = 9  'The control is disconnecting from the host computer.
Const icDisconnected = 10  'The control successfully disconnected from the host computer.
Const icError = 11 'An error occurred in communicating with the host computer.
Const icResponseCompleted = 12 'The request has completed and all data has been received.

Const icUnknown = 0    'Unknown.
Const icDefault = 1    'Default protocol.
Const icFTP = 2    'FTP. 'File Transfer Protocol.
Const icReserved = 3   'Reserved for future use.
Const icHTTP = 4   'HTTP. HyperText Transfer Protocol.
Const icHTTPS = 5  'Secure HTTP.

Const icString = 0     'Default. Retrieves data as string.
Const icByteArray = 1  'Retrieves data as a byte array.

'****************************************************************

Add following code and implied controls

Private Sub btnGetHTML_Click()
    Inet1.Protocol = icHTTP
    Inet1.Execute txtURL.Text, "GET"
End Sub

Private Sub Inet1_StateChanged(ByVal State As Integer)
    Dim vtData As Variant
    Dim intFile As Integer

    intFile = FreeFile()
    Select Case State
        ' Add other cases to follow what is taking place
        Case icHostResolvingHost
            StatusBar1.Panels(1).Text = "Connecting..."
        Case icConnected
            StatusBar1.Panels(1).Text = "Connected..."
        Case icReceivingResponse
            StatusBar1.Panels(1).Text = "Receiving..."
        Case icError
            StatusBar1.Panels(1).Text = "Error in data transfer"
            Inet1.Cancel
            Exit Sub
        Case icResponseCompleted
            ' Open a file to write to.
            Open "c:\HTMLtest.txt" For Binary Access Write As #intFile
   
            vtData = Inet1.GetChunk(1024, icString)
   
            Do While LenB(vtData) > 0
                Put #intFile, , vtData
                vtData = Inet1.GetChunk(1024, icString)
            Loop
            Put #intFile, , vtData
            Close #intFile
            StatusBar1.Panels(1).Text = "Ready"
            Inet1.Cancel
    End Select
End Sub

0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 8

Expert Comment

by:mrmick
ID: 1446249
thoba, all you need is this:

   Dim HTML$

   Inet1.AccessType = icDirect 'SET THIS ACCORDINGLY
   Inet1.URL = "http://www.yahoo.com/"
   HTML$ = Inet1.OpenURL
   Inet1.Cancel 'That's it, You've got what you need.

   Open "HTML PAGE.txt" for output as #1
   Print #1, HTML$
   Close 1


0
 
LVL 8

Expert Comment

by:mrmick
ID: 1446250
Unfortunately, I don't think you can use the INet control in Excel unless you're using Excel 97 - and then I'm not sure because I'm running Excel 95 (7.0).  Excel 95 has no means of adding the INet component.  You can create an app in VB with the above code and shell the exe from Excel using the Shell Function.  Check out "Shell" in Excel help.

0
 
LVL 4

Expert Comment

by:rantanen
ID: 1446251
mrmick, I know that is the simplest solution what you provided. It is however restricted to material in text form (of course that is enough if you want to get only the source of the HTML page), but if you want to get also the binary material (eg. images on the page) you have to do what I showed, so in that sense it is more general.

But if thoba is more satisfied with your answer, that is OK with me.
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1446252
Images referenced in an html document must be requested separately by the client after the initial page is received.  This allows a client (such as a browser) to be configured not to automatically receive image information when the data connection is slow.  No binary data is sent by the http server when a html page is requested.


0
 
LVL 4

Expert Comment

by:rantanen
ID: 1446253
I know that, but if you want to request it, then...

Another thing which might mean something if you are on a slow connection and retrieving quite a large file is the operation mode.

OpenURL is synchronous and Execute is asynchronousand can thus be made to run in the background.
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1446254
True - the OpenURL is synchronous.  But wouldn't this be another argument as to why the OpenURL is the preferred method?

I'm assuming the only purpose of the VB app is to get the html data and save it to a file... at which point the app would terminate... which would be the signal to the Excel procedure that the file is ready.

0
 

Author Comment

by:thoba
ID: 1446255
Is there no possibility to create an object by program with the createObject() function? It's OK for me to read only text.
0
 

Author Comment

by:thoba
ID: 1446256
I have now a solution. I read the htm File with a Java application called with the shell() function, which generates a local File.

Thank You for help
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
This article will show, step by step, how to integrate R code into a R Sweave document
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question