Using VBA to query data from the web

**JimmySmith** · 04-05-2012, 12:02 PM

Hello,

I am pretty new to VBA still and am having trouble getting started on an application that I would like to create so any help would be greatly appreciated. The details of what I would like to accomplish is below.

I am trying to create a foreign exchange calculator in excel using VBA to create all the functions of the application. The website I have found that I believe will work in querying the data is http://www.imf.org/external/data.htm and more specifically, the exchange rate data section. I would like to program the application to allow the user to specify what date range they would like and this is where I need help. Is there anyway to program VBA to allow the user to select specific requirements such as length of time and which exchange rates they would like then pull in the data provided by the IMF website into excel? The purpose of the application is to give the user exchange rates with real time data given a certain currency.

Thank you for your help, again any advice on this will be greatly appreciated.

**venkat1926** · 04-06-2012, 05:57 AM

open an excel file
activate sheet1

try this macro

Sub exchangerates()
Dim cfind As Range
Worksheets("sheet1").Activate
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.imf.org/external/np/fin/data/rms_mth.aspx?reportType=REP", _
        Destination:=Range("A1"))
        .Name = "rms_mth.aspx?reportType=REP"
        .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
    Set cfind = ActiveSheet.Columns("A:A").Find(what:="currency", lookat:=xlWhole)
    Range(Range("A1"), cfind.Offset(-1, 0)).EntireRow.Delete
    Set cfind = ActiveSheet.Columns("A:A").Find(what:="disclaimer", lookat:=xlWhole)
    Range(cfind, Cells(Rows.Count, "A").End(xlUp)).EntireRow.Delete
   Range("a1").Select
    End Sub

**JimmySmith** · 04-06-2012, 12:31 PM

That worked perfect! Thank you! I have another question related to this, If I were to create a user form that allowed a user to specifically pick out different parameters would I be able to communicate with html to navigate through web pages? For example on this page http://www.imf.org/external/np/fin/e...yDataBase.aspx. Could I use VBA to tell the web browser to pick specific options and dates? I know that I can look at the source code on each page but I'm not sure how to communicate with those specific objects. This maybe be extremely complicated and I would like to thank you for the help that you have given me thus far