Copy and paste from IE to book

[joeln]

I am currently using the code below to select and copy from a web page. Can anyone tell me how to select a range in this manner as I am getting a whole page when I only need 2 or 3 itmes.



code:



ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

[Rembo]

Hello Joeln,


I am currently using the code below to select and copy from a web page.


The method you are using simply copies all information and you only want part of that information. There are two possible solutions to your problem.

1) Filter out the information you want after you copied it to your workbook.

2) Filter out the information you want before writing it to your workbook.

I'll give you an example of the second option. To do so you will need to use a different method to retrieve information from the website. In this example I choose to use the Internet Explorer Document object to do so because it has many properties we can use. Alternatively you could use a XML object (CreateObject("MSXML2.XMLHTTP")) which is faster but also a bit more difficult to extract information from.

The example code below extracts the latest topics posted on VBAX and shows it in a MsgBox and writes it to the worksheet "Latest VBAX Posts", which is also created, in your workbook.

Hope that helps,

Rembo

Sub GetLatestVBAXPosts()
Dim objIE As Object
Dim i As Integer, i2 As Integer
Dim sURL As String
Dim sAllPosts As String

'Create a new Worksheet "Latest VBAX Posts"
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = "Latest VBAX Posts"

' URL to open
sURL = "http://www.vbaexpress.com/forum/search.php?do=getdaily"

On Error GoTo error_handler

' Open the URL in an Internet Explorer instance
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Navigate sURL
Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
.Visible = False
End With

' and filter out relevant information from the webpage (=document)
' In this example I grabbed the text within the links of new posts
' and stored it in the string sAllPosts and wrote it to a worksheet.
' This method also allows you to select tables and forms for example,
' Google for details.

i2 = 1
With objIE.Document
For i = 0 To .Links.Length - 1
If Left(.Links(i).href, 47) = "http://www.vbaexpress.com/forum/showthread.php?" Then
If Len(.Links(i).InnerText) > 3 Then
sAllPosts = sAllPosts & .Links(i).InnerText & Chr(13)
Worksheets(Worksheets.Count).Range("A2").Offset(i2, 0).Value = .Links(i).InnerText
i2 = i2 + 1
End If
End If
Next i
End With
Worksheets(Worksheets.Count).Range("A1").Value = "Latest posts on VBAX:"
MsgBox ("Latest posts on VBAX:" & Chr(13) & Chr(13) & sAllPosts)

' Unload Internet Explorer instance from memory before exit
Set objIE = Nothing
Exit Sub
error_handler:
MsgBox ("Unexpected Error, I'm quitting.")
objIE.Quit
Set objIE = Nothing
End Sub