Get External data from a webpage

[Guest]

Hello;
I'm not sure where to put this ?. Hope someone can provide some insight.
It may not be something I can accomplish solely w/Excel.

I want to get the foreign exchange rates everyday & store them in a master
workbook. Best reference I found so far --
http://today.reuters.com/Investing/Currencies.aspx -- if someone has
something better, like that shown in the Wall St. Journal, I'd be happy to
use that, as well.
Anyway, I'd like to use the "export to Excel" right click drop down feature,
and save the rate table in a worksheet. I wanted to automate it to retrieve
the information automatically.

I know I can schedule a task to open excel at the same time every day,
write a macro to open a workbook,
go to the site by executing a hyperlink,
once there do the right click thingy (how do I get it to do this
automatically?)
have it skip from the last end point of entry and insert new info into the
next row (can figure this out myself, sort of)
save & close the workbook
close the Excel application

How do I get it to "copy/export" from the internet automatically? Is there
even a way to do so?

As always, guidance, suggestions, alternate methods will be greatly
appreciated.

Thanks everyone.

PS

 

[mudraker]

I would use a web query to get your your date into Excel

This code returns data to sheet1 (I used record macro whilst doing a
Data Menu > Import External Data > New Web Query > Paste website URL
into address field then selecting go button

Sub GetWebData()
Sheet1.Activate
Cells.ClearContents
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://today.reuters.com/Investing/Currencies.aspx",
Destination:=Range( _
"A1"))
..Name = "Currencies"
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..BackgroundQuery = True
..RefreshStyle = xlInsertDeleteCells
..SavePassword = False
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..WebSelectionType = xlEntirePage
..WebFormatting = xlWebFormattingNone
..WebPreFormattedTextToColumns = True
..WebConsecutiveDelimitersAsOne = True
..WebSingleBlockTextImport = False
..WebDisableDateRecognition = False
..WebDisableRedirections = False
..Refresh BackgroundQuery:=False
End With
End Sub


To find last used row in column a use
LastRow = sheets("sheet2").Cells(Rows.Count, "a").End(xlUp).Row

or to find last used row in sheet use

GetBottomRow = sheets("sheet2")..Cells.Find(what:="*",
searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row

you will need to add 1 to LastRow and GetBottomRow to get the row
number for that you will use.

I have not added code to copy data from sheet1 to sheet 2 as I do not
know what data you required to copy