Help on Marcos & External Web Query - Basic Macro Skill

[gaboo]

Hi,
Wonder if anyone can help me ? I want to download a certain range of FX rate from oanda.com and import that into my excel sheet. So i did the Macro function to record the process in which I do this, Data>Get External Data> onanda.com>date selection> currency selection> import. That works out fine. However, the macro is limited to the original dates I inputed. so the code reads only 8/2~8/10. How can I change the code so that the date can be changed?? Thanks!!

Range("E6").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.oanda.com/convert/fxhistory?lang=en&date1=08%2F04%2F07&date=08%2F10%2F07&date_fmt=us&exch=USD&exch2=gbp&expr=EUR&expr2=usd&margin_fixed=0&&SUBMIT=Get+Table&format=ASCII&redirected=1" _ , Destination:=Range("E6"))
.Name = _
"fxhistory?lang=en&date1=08%2F04%2F07&date=08%2F10%2F07&date_fmt=us&exch=USD&exch2=gbp&expr=EUR&expr2=usd&margin_fixed=0&&SUBMIT=Get+Table&format=ASCII&redirected=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """converter_table"",7,8"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[kevin.philips]

Hi,

You will need to populate the following string in a different way:

http://www.oanda.com/convert/fxhist...0&&SUBMIT=Get+Table&format=ASCII&redirected=1

The parts you need to change are:

08%2F04%2F07

Which breaks down as follows:

08 = Month Number
%2F = Encoded Slash
04 = Day Number
%2F = Encoded Slash
07 = Year Number

So, as long as you only change the numbers the querystring for the website should pull the right data, of course you also need to do the same for the end date.