Extract Data to Excel from a Website.

[bjoshi]

Hi,

If you visit " iforex dot com " you will see a table of current currency rates on the right hand corner.

Please provide me with a Excel-VBA code to get the data in that particular table.

Best Regards.

[Shred Dude]

I found it easier to first navigate to the redirect page that the primary url sends you to anyway. From where I am, that goes to:

[VBA]http://www.iforex.com/index.aspx?act=lang&lang=English[/VBA]

Then, using a simple screen scrape through IE automation, you can capture the results and put them on a sheet.

Try this:

[VBA]Public Sub getCurencyRatesIE()
Dim IEapp As Object
Dim strURL As String, strData As String
Dim myTable As Object
Dim data()
Dim r As Integer, c As Integer

'Clear out previous Quotes
ActiveSheet.UsedRange.ClearContents

Set IEapp = CreateObject("internetexplorer.application")

strURL = "http://www.iforex.com/index.aspx?act=lang&lang=English"
With IEapp
.Visible = False 'True
.navigate strURL
While .busy Or .document.readystate <> "complete": DoEvents: Wend

Set myTable = .document.getelementbyid("ctlQ_tblQuotes")
While myTable.Rows.Length < 1: DoEvents: Wend
End With

ReDim data(0)
data(0) = "Symbol|Bid|Ask"

With myTable
For r = 2 To .Rows.Length - 1 Step 2
' Cause it to exit at the start of the commodities quote table
If .Rows(r).Cells.Length < 2 Then Exit For
For c = 1 To 3 'strip the Cross Rate , Bid, & Ask for each line
strData = strData & .Rows(r).Cells(c).innertext & "|"
Next c
ReDim Preserve data(UBound(data) + 1)
data(UBound(data)) = strData
strData = ""
Next r
End With

'Write Results to the Sheet
With ActiveSheet.Range("a1").Resize(UBound(data) + 1, 1)
.Value = Application.Transpose(data)
.TextToColumns DataType:=xlDelimited, other:="True", otherchar:="|"
End With
UsedRange.Columns.AutoFit

'Add a Date/Time Stamp as to when this refresh was done
ActiveSheet.Range("a" & UBound(data) + 2).Value = "Updated: " & Now

'Clean up
IEapp.Quit
Set IEapp = Nothing
Set myTable = Nothing
End Sub[/VBA]

Doing it through xmlhttp requires you to format up the cookie they assign to you from the initial page redirect, which includes your IP address. Given the small amount of data, I didn't find it to be advantageous to go that route.

You can set the visible property to true if you want to see the browser while refreshing the quotes.

[bjoshi]

Shred Dude you are the greatest. This worked like a charm. Except I get some object 424 error at the end of it but thats alright.

Thanks a ton, I have been trying to do this since the past week now.

Thank you. Thank you. Thank you.

I didnt get the "doing it through xmlhttp" part, if you can could you please share that too? And explain it a bit more if possible.

Best Regards.