Copying data from a table on a website into excel

[daivd_vachon]

Hi guys,

I am having a lot of trouble with the following. I am trying to modify a code to copy specific numbers from a webpage. The code I'm working with is:

Please Login or Register  to view this content.

Option Explicit
Option Compare Text

Sub FidelityBenchmarks()
Dim t, Tag As String, tag2 As String, EndTag As String
Dim oHttp As Object, txt$, i&, j&, x&
Dim arr
Dim cel As Range
arr = Array("head", "description", "keywords")

On Error Resume Next
Set oHttp = CreateObject("MSXML2.XMLHTTP")
If Err <> 0 Then Set oHttp = CreateObject("MSXML.XMLHTTPRequest")
If oHttp Is Nothing Then MsgBox "MSXML2.XMLHTTP not found", 16, "Error": Exit Sub
On Error GoTo 0


For Each cel In Selection
If cel <> "" Then
With oHttp
.Open "GET", Cells(cel.Row, 2), False
.Send
txt = .responseText
For x = 0 To 2
Tag = arr(x)
'Adjust this to suit
If Tag = "head" Then
tag2 = "<tbody>"
Cells(cel.Row, 3).Offset(, x) = GetTitle(txt, tag2, "<", 0)
Else
tag2 = Tag & Chr(34) & " content="
Cells(cel.Row, 3).Offset(, x) = GetTitle(txt, tag2, Chr(34), 1)
End If
Next
End With
End If
Next
Set oHttp = Nothing
End Sub



Function GetTitle(txt, tag2, EndTag, Bit)
Dim i As Long, t
i = InStr(1, txt, tag2, 1)
If i = 0 Then
GetTitle = "not found"
Else
t = Split(txt, tag2)(1)
GetTitle = Trim(Split(t, EndTag)(Bit))
End If
End Function

Please Login or Register  to view this content.

The code Copies the titles from the webpages found in column B. I need to modify it to copy specific numbers from table from webpages like this,

http://www.fidelity.ca/cs/Satellite/.../overview/cpin

For example if i wanted to have the macro copy the 3 month Benchmark return (2.16%), how would I achieve this? If anyone could point me in the right direction it would be greatly appreciated. I have to do this for over 100 webpages on a monthly basis and this would save me a lot of time.

Cheers

[daivd_vachon]

My main problem is know what to reference from the webpage source, does anyone have any good ressources I could read to try and figure it out?

[mtt23]

What IE are you using? I find if you use google chrome it is particularly useful because you can right click on an object and then 'Inspect Element'. This will help you try to reference objects from the web pages.
If you don't have it just quickly download it and check it out. You can always uninstall it.

[daivd_vachon]

I will take a look, hopefully it helps. Thank you.

[daivd_vachon]

I am still having trouble with this. For the mean time I am using another code to grab the entire table and paste it into excel by referencing its table #. I would love to be able to just get the numbers I need however. I still haven't found any good resources for this. Does anyone know of any?