There is such a site xe.com, there means currency exchange at the current rate goes, and the rate is updated every minute. I did not create a large table in Excel calculation of rubles in Euro at a specific rate, but since the course changes, then I need Excel to take the updated course from the site, and place this course, for example in cell S1. As soon as I launched the Excel, the course was immediately updated and all the data where this figure is used (how many 1 ruble per euro) are automatically updated ... something like that ..

  • one
    Just be careful - as far as I remember, you are thus violating the license to use data from xe.com - I’ve already tried to rude their lawyers :) - Barmaley

3 answers 3

You can use this function - it fills the first cell with a course:

Sub getRurEurRate() Dim xhr As MSXML2.XMLHTTP60 Dim doc As MSHTML.HTMLDocument Dim table As MSHTML.HTMLTable Dim row As MSHTML.HTMLTableRow Dim cell As MSHTML.HTMLTableCell 'get page content Set xhr = New MSXML2.XMLHTTP60 With xhr .Open "GET", "http://www.xe.com/?c=RUB", False .send If .readyState = 4 And .Status = 200 Then Set doc = New MSHTML.HTMLDocument doc.body.innerHTML = .responseText Else MsgBox "Error" & vbNewLine & "Ready state: " & .readyState & _ vbNewLine & "HTTP request status: " & .Status End If End With 'get rate from table Set table = doc.getElementById("xRatesBxTable") Set row = doc.getElementsByTagName("tr").Item(8) Set cell = row.getElementsByTagName("td").Item(2) ' Put to excel cell Cells(1, 1).Value = cell.innerText End Sub

This is a working code, but rather clumsy. The course is taken from a specific cell, and if, say, one more row is added to the table before the desired one, it will not work correctly. So this is just an implementation for you, from which you can make a start.

And you need to enable Tools - References in the vba editor:

  • Microsoft HTML Object Library
  • Microsoft XML, v6.0

    In PHP this is done using the PHPExcel library (which allows you to read and write any data in the exel). In the case of updating data in Excell. On Go (golang) this is done using this library. There are also similar libraries in other programming languages ​​( python , Ruby ), but I recommend paying attention to PHPExcel because It is time tested and can work with xls 2003 and xlsx 2007+. I’m not sure about the update of the cell, but the whole file will be updated without any problems. But if we talk about the web , then let's say on the "site" there will be a link to the file, clicking on which you receive a file with updated data each time. Updating the file occurs on the cron scheduler for example. Or by pressing a button.

    • I read everything, and did not understand anything :-( And why php? Excel 2016 - Ratevaio
    • @Ratevaio Yes, in fact, with proper knowledge of VBA, you can write on it, in Excel itself - Sublihim
    • With the help of what I wrote above, you can get fresh data every minute (hour, day ...). You just mentioned the web and php is like something that looks exactly in this direction (on other web technologies this is unlikely to work). If you plan to put tens of thousands of lines in the xls file, the PHPExcel library will cope with this (I checked it myself). If you need to pull one line, then you can VBA . - fonjeekay

    No programming.

    Excel has a built-in tool for connecting external data.

    The Data menu, the Get External Data section - from the Internet . The search engine window opens. In the search bar enter the address of the desired site, press Enter .

    After loading the page on the tables that can be imported into Excel , there are arrows on a yellow background. When you click on the arrow, it turns into a daw, the background turns green. This means that the table is selected for import into Excel . A click on the checkbox returns the arrow to its original state.

    After selecting the tables, click the Import button (in the lower right corner). In the Import data window that appears, specify the location cell of the imported data. Here you can adjust the properties of the range. OK - the connection is made, the data is inserted on the sheet.

    Range properties can be configured and after import - click RMB on any of the unloading cells.

    The data is updated when you open the book or when you select from the menu (right-click on any of the unloading cells).

    More information can be obtained by asking any search engine:

    Excel Get external data from the Internet