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
Re: Internet Explorer Select All
Try the commands
ie.ExecWB 17, 0 '// SelectAll
ie.ExecWB 12, 2 '// Copy selection
Re: Internet Explorer Select All
Why not use Data>Import external data>New Web query.
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
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 SubRe: 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.
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.
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
Don’t have an account yet? Register yourself now and be a part of our community!