Excel Custom Function to Retrieve XML Data

Question

I would like to create a custom function that would retrieve specific data available in the form of XML URL link. This is the first time I am dealing with XML in Excel, let alone VBA, and am struggling with it.

I have been able to write a procedure (local currency exchange rate retrieval) which one can run by simply pressing a custom button, which runs the code below:

Sub FX_Retrieve()
    Dim FX As String
    Dim CustomDate As String

    FX = Range("FX")
    CustomDate = Range("CustomDate")

    Application.ScreenUpdating = False

    ActiveSheet.Unprotect 
    Rows(2).Clear
    ActiveWorkbook.XmlImport URL:= _
        "http://www.cbu.uz/section/rates/widget/xml/" & FX & "/" & CustomDate, ImportMap:= _
        Nothing, Overwrite:=True, Destination:=Range("C2")
    Range("D:D").EntireColumn.AutoFit
    Rows(2).HorizontalAlignment = xlCenter
    Columns(6).ClearContents
    ActiveSheet.Protect 

    Application.ScreenUpdating = True
End Sub

Just in case, the XML output looks like this: 2015-12-29 USD 2809.98 1 Hence, the need for some formatting in my code.

No problem with that procedure in the case of a single call for the FX rate! Now, I want to go a bit further than that and create a function. And this is where I come up with several problems:

I need to extract specific child node (rate) from the xml output, which looks as this:

'

<response>
<date_act>2015-12-29</date_act>
<symbol>USD</symbol>
<rate>2809.98</rate>
<size>1</size>
</response>

I have found something similar already (as in here: Excel VBA getting specific node from XML), yet I am not able to apply it in my case.

the creation of function as far as I can see it will not allow the same approach as I had in my procedure.

Any help would be much appreciated!

Tango_Mike · Accepted Answer · 2016-02-03T10:37:33.863

Thanks to the AnalystCave.com website run by @Dick Kusleika, I have been able to adapt the information presented there on the topic of "Working with XML files" to my problem and have successfully resolved it. Just in case someone has the same problem, here is my code (I just hope it is programmatically efficient):

Public Function GetCurrToUZS(ByRef Curr As String, ByRef date_param As Date) As Currency
    Dim XDoc As Object
    Dim lists
    Dim getThirdChild
    Dim corrDate As String

    If Len(Curr) <> 3 Then
        MsgBox "Currency name must be 3 letters long!", vbCritical + vbOKOnly _
            , "VARIABLE ERROR!"
        Exit Function
    End If
    'corrects 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")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load "http://www.cbu.uz/section/rates/widget/xml/" & Curr & "/" & corrDate

    'Get Document Elements
    Set lists = XDoc.DocumentElement
    Set getThirdChild = lists.ChildNodes(2)

    GetCurrToUZS = getThirdChild.Text 'output of the function

    Set XDoc = Nothing 'terminates xDoc Object
End Function

Excel Custom Function to Retrieve XML Data

1 Answers1