VBA to copy particular webpage content

[Trebor76]

Does anyone have code to copy the contents of a text file from a webpage?

For instance, from this page I only want the data in the text box, which can be selected by clicking the Highlight All button.

I've seen code to copy an entire page, but this does not capture the text box contents in this case.

Any help would be appreciated.

Robert

 

[Leith Ross]

Hello Norie,

A common problem I have encountered is the Document object being "nothing" even after the Internet Explorer has finished loading the web page. The While loop is used to determine when the page has finished by checking both the "Busy" and "ReadyState" flags.

Here is an example:

Dim ieApp as Object
Dim ieDoc As Object
Dim URL As Text

    URL = "www.somewebpage.com"

    Set ieApp = CreateObject("InternetExplorer.Application")
    ieApp.Navigate URL
    ieApp.Visible = True

    While ieApp.Busy Or ieApp.ReadState <> 4: DoEvents: Wend

        Set ieDoc = ieApp.Document

It is not uncommon to see a delay included in the code to allow for the document to "settle" before accessing it. Even then, this is not always a fail-safe method.

Before using Shell to access the Document object, I would use a loop to check if the Document object was "nothing" and exit once set. It is obvious that this approach is flawed as well.

    While ieDoc Is Nothing: Set ieDoc = ieApp.Document: DoEvents: Wend

The flaw is not only having to manually break loop if it hangs but also another interesting problem arose. Occasionally, the Document variable would be set to an object but not the Document object. Something I have never been able to figure out the cause of.

 

[Norie]

Leith

This happens a lot with ASP pages.

As well as checking the page is loaded using a loop you can use a similar loop to check if the document is 'complete'.

You can even stick an Application.Wait in the code.

That's something which I really don't like to do but sometimes it seems necessary even though it will slow the code down more than looping.

If you have problems with getting stuck in a loop you can add some other check, eg no of iterations, length of time looped.

The thing about these two techniques are they are kind of arbritary, for example how do you decide how long to wait or how many times to iterate.

Anyway, that's my experience/opinion but I suppose the most important thing is to get the code to work and do what you want it to.

So whatever method does the job is fine.

 

[Leith Ross]

Hello Norie,

Even though adding the additional code to create the Shell seems "excessive" to most people, I prefer to have the code work reliably and not have to "guess" if the objects are available. The additional testing and checking not only slows the process down but also complicates otherwise simple code.

 

[Norie]

I only really suggested one additional test - checking the document is complete.

The other things I mentioned I would be loath to use but sometimes they're needed.

 

[rohitrik]

Hi Leith,

I am following this post to automate download. I have 3 requirements:
1) To download text file from internet onto my machine.
2)To open the downloaded text file into excel and do the necessary formatting and save as excel workbook.
3)Attach the saved excel file to my mail.

Tasks 2 and 3 have been automated. But i am wondering how to get 1 done. Have spent a lot of time in googling but in vain.

To be more precise, to download the text file i have to search it among other files and then right click onto it and click save target as to save it onto my local machine.

Any sort of help would be highly appreciated.

Thanks in advance.