Announcement

Collapse
No announcement yet.

Pull Web Page Into Worksheet

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Pull Web Page Into Worksheet



    Split off from http://www.ozgrid.com/forum/showthread.php?t=69569
    Hey Norie:

    Thanks for the code. I can make use of that. Can you use something like that to pull "All the text" from that URL back to Excel (like in Select All, Copy, PasteSpecial) rather than using SendKeys? The existing URL listed in your Test Sub would be fine to use in the example. If so, could you please modify the Test Sub code to show the code?

    Thanks,

    JerryD
    Last edited by Dave Hawley; June 15th, 2007, 08:48.

  • #2
    Re: Internet Explorer Select All

    Try the commands

    ie.ExecWB 17, 0 '// SelectAll
    ie.ExecWB 12, 2 '// Copy selection
    Kind Regards,
    Ivan F Moala From the City of Sails

    http://www.xcelfiles.com

    Comment


    • #3
      Re: Internet Explorer Select All

      Why not use Data>Import external data>New Web query.

      Comment


      • #4
        Re: Pull Web Page Into Worksheet

        Thanks to Norie, Tom, Ivan, and Dave for your responses. All your suggestions work fine.
        Here is Norie's Test code modified with Ivan's ExecWB commands, which work fine for me.
        This code will copy the Web page to the Clipboard and then paste the clipboard into Excel
        (the PasteSpecial command will paste only the text, omitting links and objects)

        Thanks again,
        Jerry D

        Code:
        Sub Test()
            Dim IE As Object
            
            Sheets("Sheet3").Select
            Range("A1:A1000") = "" ' erase previous data
            Range("A1").Select
            
            Set IE = CreateObject("InternetExplorer.Application")
            With IE
                .Visible = True
                .Navigate "http://www.aarp.org/" ' should work for any URL
                Do Until .ReadyState = 4: DoEvents: Loop
            End With
        
            IE.ExecWB 17, 0 '// SelectAll
            IE.ExecWB 12, 2 '// Copy selection
            ActiveSheet.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False
            Range("A1").Select
            IE.Quit
            IE.Quit ' just to make sure
        End Sub

        Comment


        • #5
          Re: Pull Web Page Into Worksheet

          Jerry

          Good to see you've got something working.

          Rather than using the select all and paste method why not try this.
          Code:
          Sub Test()
          Dim IE As Object
               
              Set IE = CreateObject("InternetExplorer.Application")
              With IE
                  .Visible = True
                  .Navigate "http://www.aarp.org/" ' should work for any URL
                  Do Until .ReadyState = 4: DoEvents: Loop
          
                  x = .document.body.innertext
                  x = Replace(x, Chr(10), Chr(13))
                  x = Split(x, Chr(13))
                  Range("A1").Resize(UBound(x)) = Application.Transpose(x)
                  
                  .Quit
              End With
              
          End Sub
          The reason I prefer something like this is because you have more control over what you are actually getting from the page.

          The above code extracts all the text but code can also be used to, for example, identify a particular table(s) and extract the data from that.
          Boo!

          Comment


          • #6


            Re: Pull Web Page Into Worksheet

            Yes, I like this. No need for the clipboard. No need for the Sendkeys. You've gone the extra mile Norie. Thanks.

            JerryD

            Comment

            Working...
            X