VBA to copy particular webpage content

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
5,342
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hello Robert,

This macro will copy the text from the web page to the clipboard.
Code:
' Thread:  http://www.mrexcel.com/forum/showthread.php?t=603712
' Poster:  Trebor76
' Written: January 07, 2012
' Author:  Leith Ross

Sub CopyWebText()

    Dim I As Long
    Dim ieApp As Object
    Dim ieDoc As Object
    Dim ieBtn As Object
    Dim ieBtns As Object
    Dim Shell As Object
    Dim Text As String
    Dim URL As String
    
        URL = "http://www.abs.gov.au/AUSSTATS/abs@.nsf/webpages/ABS%20Release%20Calendar%20Export?opendocument"
        
        Set ieApp = CreateObject("InternetExplorer.Application")
        
            ieApp.Navigate URL
            ieApp.Visible = True
            
            While ieApp.Busy Or ieApp.ReadyState <> 4: DoEvents: Wend
            
            Set Shell = CreateObject("Shell.Application")
            Set ieDoc = Shell.Windows(0).Document
            
                Set ieBtns = ieDoc.getElementsByTagName("input")
                
                For I = 0 To ieBtns.Length
                    If ieBtns(I).Value = "Highlight All" Then
                       Set ieBtn = ieBtns(I)
                       Exit For
                    End If
                Next I
                
                If ieBtn Is Nothing Then MsgBox """Highlight All"" button not found.": Exit Sub
                
                ieBtn.Click
                
                ieApp.ExecWB 12, 0
                
End Sub
 
Upvote 0
Hey Leith,

Thanks for this - I've spent a day on it so far :(

I'm getting a 'Run-time Error 91' error message when 'I' equals 25. Also, can I paste the contents into a text (notepad) file?

Thanks for the help Leith, I realyy appreciate it.

Robert
 
Upvote 0
Hello Robert,

Sorry about the "I" going over. The count is always one more than the index. The corrected code line would be...
Code:
For I = 0 To ieBtns.Length - 1
Did you want the macro to paste the text into a new Notepad file?
 
Upvote 0
Sorry to be a pain, but now it's (the code) is saying that it cannot find the "Highlight All" button when it clearly exists?

Did you want the macro to paste the text into a new Notepad file?

Yes, that would be great!!
 
Upvote 0
Hello Robert,

What versions of Windows and Internet Explorer are you using?
 
Upvote 0
Windows is XP and Internet Explorer is 8.

There is more than one web page open - does the focus have to be set to the ABS site?
 
Last edited:
Upvote 0
I'm guessing - but is it to do with the static nature of this line:

Code:
Set ieDoc = Shell.Windows(0).Document

If so, is there a way to loop through each open Internet Explorer to set the zero to the correct document number?

Cheers,

Robert
 
Upvote 0
I agree with you.
g.php
 
Upvote 0
Hello Robert,

Sorry for the delay. I had to take my wife to pick up her prescriptions. The long was incredibly long. Didn't think I would make it back tonight.

Still working on few ideas. Do you have more than one instance of Internet Explorer running or just multiple tabs open in it?
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top