从互联网获取正确的汇率 [英] Get the right exchange rate from the internet

问题描述

对于我的Excel计算表，我需要实际汇率.从互联网上查找并复制汇率是没有问题的.我的问题是，我从Internet Explorer复制的值不会显示为正确的数字格式.

For my Excel calculation table I need the actual exchange rate. Find and copy the exchange rate from the internet is no problem. My problem is, that the value which I copy from the internet explorer will not shown as the right number format.

例如，我想要阿根廷比索的汇率(€1 = 49,9341阿根廷比索).我复制了来自Internet Explorer的值，并希望将其粘贴到工作表中，不幸的是，该值的数字格式将显示为€1 = ARS 499.341

For example, I want the exchange rate for the Argentinian Peso (€1 = ARS 49,9341). I copy the value from the internet explorer and want to past it into my sheet, unfortunately the number format of the value will be shown as €1 = ARS 499.341

这是我的代码:

'Create Internet Object
Set IE = CreateObject("InternetExplorer.Application")

'IE.Visible = True
IE.Navigate "https://www.finanzen.net/waehrungsrechner/euro_" & currency_value

Do
    DoEvents
Loop Until IE.readystate = 4

value_to_copy = IE.Document.GetElementByID("currency-second-input").Value
currency_value = Split(value_to_copy, " ")
    
Range("L12").Value = "Price in " & currency_value(0)
Range("K8").Value = currency_value(1)  'This value will shown as 499.341

以下是我复制网站汇率的HTML部分:

Here the HTML-part where I copy the exchange rate of the website:

<input value="USD 1,1189" id="currency-second-input" onclick="this.select();" onkeyup="calculateCalculator(true, 'currency-second-input', false)" style="width: 50%; float: left; height: 51px; padding: 10px; font-size: 20px; color: #0066cc; font-weight: bold;" type="text">

在我的测试运行中，并非总是会出现此问题.如果我需要哥伦比亚比索(€1 = COP 3.623,5878)的汇率，它将超过正确的值.

In my test runs this problem doesn't always occur. If I need the exchange rate for the Colombian peso (€1 = COP 3.623,5878), it will past the right value.

推荐答案

如果您不介意它是一个字符串，则可以在其前面粘贴'".您可以使用更快的xhr而不是打开浏览器.

If you don't mind it being a string you could paste with "'" in front. You can use faster xhr rather than opening a browser.

Option Explicit
'VBE > Tools > References: Microsoft HTML Object Library
Public Sub ExchangeRate()
    Dim html As Object
    Set html = New HTMLDocument
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.finanzen.net/waehrungsrechner/euro_argentinischer-peso", False
        .send
        html.body.innerHTML = .responseText
    End With
    ActiveSheet.Cells(1, 1) = "'" & html.getElementById("currencyExchangeRate").Value
End Sub

这篇关于从互联网获取正确的汇率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！