Run .IQY within VBA

[santiagosequera]

Hey everyone...i'm sorry if this question has already been answered but it's kinda hard going through 33k pages of MS Excel related threads and the forum search engine doesn't work too well...i'm working with MS Excel and i need one single number from a web page....its the dollar exchange rate for my country nothing more....so i thought the best way to do this was to create a web query ( Data -> Import External Data -> New Web Query )...enter this address hxxp://www.grupoaval.com/oc4j/portales/jsp/historicoindicadores.jsp?indi=1....and it shows you a list of all historic values for the last month untill present day......all i need is the latest value.....so i created the .IQY and this is what i got

WEB
1
http://www.grupoaval.com/oc4j/porta...sp?indi=1&fecini=05/23/2008&fecfin=07/09/2008

Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

and if i run that on Excel.....it gets me the complete list shown on the web page....since the data is always show from A1 downwards.....the value i will always need is located in B1.....so in the end, what i want to do is create a macro for a command button or something like it, that when i click it...it fetches the data from the web page, paste it on a worksheet called "TRM GrupoAval", copy the value from B1 in "TRM GrupoAval" to another worksheet, and after that.....dispose or close the worksheet "TRM GrupoAval".....thats what i'm trying to do in a nutshell......so since i can't seem to get the data from the web page using regular QueryTables in VBA...i thought maybe running the .iqy from VBA....i'm going to post my VBA code...

Private Sub BotonRef_Click()
strName = "TRM GrupoAval"
ThisWorkbook.Worksheets.Add
ActiveSheet.Name = strName

Dim qt As QueryTable
Set qt = ActiveSheet.QueryTables.Add(Connection:="URL;http://www.grupoaval.com/oc4j/portales/jsp/historicoindicadores.jsp?indi=1", Destination:=ActiveSheet.Range("A1"))

With qt
.WebSelectionType = xlSpecifiedTables
.WebTables = "7"
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.EnableRefresh = True
.RefreshPeriod = 5
.Refresh
End With

Set qt = Nothing

End Sub

so any help here here would be greatly appreciated....thanks a lot for the person who takes time to read and post.