My function extracts exchange rate from an external website.
I can extract a single rate for a specific date.
I get error 91 when I have a list of different dates and I copy-paste the function to that whole list. (I tell Excel to apply this function for each specific date.)
Here's my code (the credit for xDoc object creation approach goes to AnalystCave at analystcave.com/vba-xml-working-xml-files/):
Public Function GetCurrToUZS(ByRef Curr As String, ByRef date_param As Date) As Currency
Dim xDoc As Object
Dim xParent As Object
Dim getRateChild As Object
Dim corrDate As String
On Error GoTo errorHandler:
If Len(Curr) <> 3 Then
MsgBox "Current identifier should be 3 letters in lenght", vbCritical + vbOKOnly _
, "ERROR!"
Exit Function
End If
'transforms the entered date to the required format of "YYYY-MM-DD"
corrDate = Year(date_param) & "-" & Month(date_param) & "-" & Day(date_param)
Set xDoc = CreateObject("MSXML2.DOMDocument")
With xDoc
.async = False
.validateOnParse = False
.Load "http://cbu.uz/ru/arkhiv-kursov-valyut/xml/" & Curr & "/" & corrDate & "/"
End With
'Get Document Elements
Set xParent = xDoc.DocumentElement
Set getRateChild = xParent.ChildNodes(0).ChildNodes(7)
GetCurrToUZS = getRateChild.Text 'output of the function
Set xDoc = Nothing 'terminates xDoc Object
Exit Function
errorHandler:
MsgBox Err.Number, vbCritical + vbOKOnly, "Critical Error!"
Exit Function
End Function
As an example of the error, I have created this small Excel file on Dropbox (https://www.dropbox.com/s/dg2j6o4xjr9v488/FX%20Rate%20Extraction%20Error%20%28stackoverflow%29.xlsx?dl=0) with a list dates. The first one is completed using this function and should extract the rate easily without any error. Upon copy-pasting the formula to all the other dates, an error 91 will occur.
