Parsing xml values in vba

R

rustersen

Registered User.
Local time
Today, 16:25
Joined
Feb 20, 2010
Messages
13
Hi all,
I am trying to pull the currency exhange rates from a website for use in an estimate calculation form for my company.

The following will display the content of the xml file in the debugger, but how can I add criteria, so I can reference a single currency?

Code: 
Sub GetExchRate()
   Dim objXML             As Object
   Dim strURL              As String
 
   Set objXML = CreateObject("MSXML2.XMLHTTP")
 
    strURL = "h ttp://w ww.nationalbanken.dk/dndk/valuta.nsf/valuta.xml"
    objXML.Open "GET", strURL, False
    objXML.send
 
   Debug.Print objXML.responseText
End Sub

Any help is very appriciated..
 
Thank you for your reply!
I have tried your code, but I get a 'Compile error: User-defined type not defined' error on DOMDocument. Do you know why? I am using Access 2007...

jdraw said:
see my TimegenieExchangeRate procedure at
htt p://ww w.accessforums.net/queries/exchange-rate-table-query-28974.html Post #6
Click to expand...
 
If you look in the code snippets post #4 I think, you will see that you must set a reference.

'
' **********This requires a reference to Microsoft XML, v6.0 **************
Click to expand...

You may have a newer version of Microsoft XML -
I have access 2003 and M$oft xml 6.0

Look through your MISSING references, and add a Microsoft xml reference


I tried to create a proc to get the exchange rates from the site you mentioned. I could not get it to work - I could not identify any nodes. I worked at it for a while and found a bad character in one of the currency records RON "Rumke lei" was the currency. The problem was in the description between the m and k.

I copied the xml, and deleted that 1 bad character; saved the file to my machine; adjusted the proc to read my xml file, AND it worked.

I'm going to see if there is a way to identify a bad character or parse error when setting up to do these things.

Anyway, I found I could not download the xml and use it with the bad character. But since you are in Denmark, perhaps that character will not be an "error" and cause failure.

Good luck with your project.
 
Last edited:
out of interest, there is a single boolean function in the msxml library that detemines whether the xml file is well-formed - although if the whole file is displaying in a browser i suspect it is.

it is one thing knowing an xml file is well formed, and another getting the data out of it as you are finding. you still need to understand the precise structure.
 
Thanks Dave. Yes getting data out is something, but I saw a demo somewhere and created a proc to use the DomDocument to get some exchange rates from different online sources.
I haven't stumbled onto that boolean you mentioned, but when I was debugging and looking at locals from this Denmark site, I went down the tree and found a ParseError which pointed to a bad character in the Response from the website.

I captured the data and pasted it in TextPad. It showed me a ? indicating an unprintable. I removed the character, saved the file, then ran the DOM parsing stuff against my file (without the bad character) and everything worked.

If you know of a good tutorial showing xmlhttp and vba using DOMDocument, IXMLDOMNodeList etc I would appreciate the link.

I'm finding dribs and drabs of info, and a lot of it is javascript related/oriented.

Just found more info
http://msdn.microsoft.com/en-us/library/windows/desktop/ms757019(v=vs.85).aspx
 
i just checked - it isn't a boolean - i use parseerror to determine whether the xml file is well-formed


dim myxmldoc as DOMDocument

If myxmldoc.parseError <> 0 Then
etc


probably like you though - i picked up the bits i needed trying things out. it helps to understand what pointers do of course. i wish i could understand XSLT files!
 
Yes I agree. I'm pretty sure you and I discussed this a few years ago and did what we had to just parsing as a text file Mid, Instr, Right, Left, Instr etc.

I've been retired 5 years, so this is a hobby at best. Don't have work commitments, training courses -- just the odd question on a forum and a bit of time to try to resolve/work around an issue.

I did do some stuff, while at work, using XMLSpy free version, some vba and some provided to me web services procedure to test an Oracle/java/web based system.
I had to create some xsl to translate xml to plain text and used the results in Access to compare local data with data from the Oracle database, and a partners SQL server.

But you forget that (almost in hours) when you've been away and from not using it for a long time.

Any way I'll look at the ParseError method or attribute of the DomDocument and see if I can make something work.

You can see some of the stuff I've been working with at
http://www.accessforums.net/queries/exchange-rate-table-query-28974.html

Take care.
 
Again, thank you for your help!
Like you mentioned, I had to activate MSXML 6.0 in the VBA editor.

The currency xml i am using does include some danish letters, but I managed to pull the currency rates I need ( USD & EUR ) using this code:

Code: 
Dim objXML As MSXML2.DOMDocument
Dim EUR_Rate As Variant
Dim USD_Rate As Variant
    'Create the DomDocument Object
    Set objXML = CreateObject("MSXML2.DOMDocument")
    'Load entire Document before moving on
    objXML.async = False
    'Don't Validate
    objXML.validateOnParse = False
    objXML.Load_
("htt p://ww w.nationalbanken.dk/dndk/valuta.nsf/valuta.xml")
 
EUR_Rate = objXML.documentElement.childNodes(0).childNodes(0).Attributes.getNamedItem("rate").Text
USD_Rate = objXML.documentElement.childNodes(0).childNodes(1).Attributes.getNamedItem("rate").Text
MsgBox EUR_Rate
MsgBox USD_Rate


jdraw said:
If you look in the code snippets post #4 I think, you will see that you must set a reference.



You may have a newer version of Microsoft XML -
I have access 2003 and M$oft xml 6.0

Look through your MISSING references, and add a Microsoft xml reference


I tried to create a proc to get the exchange rates from the site you mentioned. I could not get it to work - I could not identify any nodes. I worked at it for a while and found a bad character in one of the currency records RON "Rumke lei" was the currency. The problem was in the description between the m and k.

I copied the xml, and deleted that 1 bad character; saved the file to my machine; adjusted the proc to read my xml file, AND it worked.

I'm going to see if there is a way to identify a bad character or parse error when setting up to do these things.

Anyway, I found I could not download the xml and use it with the bad character. But since you are in Denmark, perhaps that character will not be an "error" and cause failure.

Good luck with your project.
Click to expand...
 
Glad it was of some value.

Do you have a link to information or a tutorial re ?
EUR_Rate = objXML.documentElement.childNodes(0).childNodes(0).Attributes.getNamedItem("rate").Text
USD_Rate = objXML.documentElement.childNodes(0).childNodes(1).Attributes.getNamedItem("rate").Text
Click to expand...

I have not found anything with Domdocument and vba with a good explanation/examples.

Is there a way to use some vba and get the names of all the nodes and the hierarchy without knowing the details of the xml file?

Just for your interest I'll include the code I got working. Hmmm it's on my other PC, so I'll send it separately.
 
rustersen,

Any info on tutorials or samples regarding the Document element, childnodes etc would be most appreciated.

Here's the code I got to work last night - including the use of my copy of the xml file with the bad character removed.

I'm not sure how you're suppose to convert from a currency to DKK. The comments would seem to indicate this many units of the currency = 1 DKK. However, when I run the code and check an independent exchange rate service, the number seems to be off by a factor of 10.


Code: 
'---------------------------------------------------------------------------------------
' Procedure : DemarksNationalBank_ExchangeRate
' Author    : Jack
' Date      : 17-10-2012
' Purpose   : I've been trying to get this to work but I'm finding some bad characters in the xml file
' I can't seem to find any nodes   the length is always 0??????????? JED
'
' I found the bad character and deleted that line from the xml. Then saved that xml to
' my c drive as
'   C:\Documents and Settings\Garay\My Documents\DenmarkBank.xml
'
' I added code to read that file and use it to do the exchange rate extraction.
'
' ***** Bottom line   -- the bad character prevented working the DOM and the xml
'
'
' From a learning view, this is sending an XMLHTTP request and getting a Response and
' using the DOMDocument amd related objects to extract the required data
' from the xml response. This approach seems cleaner and more consistent than using
' vba functions to search and parse strings within the response.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency:
' **********This requires a reference to Microsoft XML, v6.0 **************
'--------------------------------------------------------------------------
'
Sub DemarksNationalBank_ExchangeRate()

    Dim arr(34, 1) As String
    Dim Resp As New DOMDocument
    Dim Req As New XMLHTTP
    Dim objNodeList As IXMLDOMNodeList
    Dim objNode As IXMLDOMNode
    Dim objAttribute As IXMLDOMAttribute
    Dim objChildNodes As IXMLDOMNodeList
    Dim mCurrency As String
    Dim mRate As String
    Dim mTime As String
    Dim mDesc As String
    Dim x As Integer
    '
    ' Added this code to get my copy of the xml without the bad character *****
    '
    Dim fso As FileSystemObject
    Dim MyFile As TextStream
    Dim MyXML As String
    Set fso = New FileSystemObject
    Set MyFile = fso.OpenTextFile("C:\Documents and Settings\Garay\My Documents\DenmarkBank.xml", ForReading, False)
    MyXML = MyFile.ReadAll
    Dim i As Integer

    On Error GoTo DemarksNationalBank_ExchangeRate_Error

    Req.Open "Get", "http://www.nationalbanken.dk/dndk/valuta.nsf/valuta.xml", False
    Req.send

    While Req.Status <> 200
        Debug.Print "waiting for response*** info"
        DoEvents
    Wend

    Resp.loadXML MyXML  'Req.responseText '''had to use my own file ****

    ' uncomment the next line to see the response
    Debug.Print "XML Response is " & vbCrLf & "~~~~~~~~~~~~~~~~~" & vbCrLf & Req.responseText

    x = Resp.getElementsByTagName("dailyrates").Length    'how many nodes
    ' Debug.Print "The number of these nodes is   " & x

    Set objNodeList = Resp.getElementsByTagName("dailyrates")
    For Each objNode In objNodeList
        If (objNode.Attributes.Length > 0) Then
            ' Get the "time" attribute for the date to which these exchange rates apply
            Set objAttribute = objNode.Attributes.getNamedItem("id")
            If (Not objAttribute Is Nothing) Then
                Debug.Print "Exchange Rates from DenmarksNational Bank   as at  " & objAttribute.text & vbCrLf _
                            & vbCrLf & "**Read these as 1  of these units = xx DKK" & vbCrLf
                mTime = objAttribute.text
            End If
        End If
    Next
    '----------


    Set objNodeList = Resp.getElementsByTagName("currency")

    For Each objNode In objNodeList
        If (objNode.Attributes.Length > 0) Then
            ' Get the "desc" attribute for these exchange rates
            Set objAttribute = objNode.Attributes.getNamedItem("desc")
            If (Not objAttribute Is Nothing) Then
                mDesc = objAttribute.text
            End If
            ' Get the "code" attribute
            Set objAttribute = objNode.Attributes.getNamedItem("code")
            If (Not objAttribute Is Nothing) Then
                mCurrency = objAttribute.text
                arr(i, 0) = mCurrency

            End If
            ' Get the associated "rate" attribute
            Set objAttribute = objNode.Attributes.getNamedItem("rate")
            If (Not objAttribute Is Nothing) Then
                mRate = objAttribute.text
                arr(i, 1) = mRate
                i = i + 1
            End If
            'Put the data in my variables for display
            If mCurrency > " " And mRate > " " Then
                Debug.Print mTime & "   " & mCurrency & "  " & mDesc & "  " & mRate
            End If
        End If
    Next objNode
    'Debug.Print "filled array"

    On Error GoTo 0
    Exit Sub

DemarksNationalBank_ExchangeRate_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure DemarksNationalBank_ExchangeRate of Module xmlhttp_Etc"

End Sub


Update: Since the post, I learned of ParseError. I used the original code again with an error routine and got this message.

There was a Parse error -1072896760 An invalid character was found in text content.
<currency code="RON" desc="Rum
Click to expand...

Here's the ParseError cheking code

Code: 
 .....
 Resp.loadXML Req.responseText   '''had to use my own file ****  MyXML
 If Resp.parseError <> 0 Then
 MsgBox "There was a Parse error " & Resp.parseError.errorCode _
    & "  " & Resp.parseError.reason _
    & "  " & Resp.parseError.srcText
    
 Debug.Print "There was a Parse error " & Resp.parseError.errorCode _
    & "  " & Resp.parseError.reason _
    & "  " & Resp.parseError.srcText
    
 End If
.....
 
Last edited:
I'm afraid I never really found any good tutorial, I used snippets of code from misc. webpages.

I did read a bit on htt p://ww w.w3schools.com/dom/dom_httprequest.asp
to get a better understanding, but my final code was made up of different sites.

For a very long time I believed SelectSingleNode would be my approach, but I never got it working. (Let me just add I am still a VBA novice, and I have no xml experience whatsoever)

I think this page got me started on childnodes: htt p://ww w.devx.com/getHelpOn/10MinuteSolution/20382

Also, this helped me on loading the xml file:
htt p://ww w.automateexcel.com/2005/06/12/what_is_a_domdocument/

I'm sorry I can't be more precise, I should have been better at saving my links! :)

jdraw said:
rustersen,

Any info on tutorials or samples regarding the Document element, childnodes etc would be most appreciated.

Here's the code I got to work last night - including the use of my copy of the xml file with the bad character removed.

I'm not sure how you're suppose to convert from a currency to DKK. The comments would seem to indicate this many units of the currency = 1 DKK. However, when I run the code and check an independent exchange rate service, the number seems to be off by a factor of 10.


Code: 
'---------------------------------------------------------------------------------------
' Procedure : DemarksNationalBank_ExchangeRate
' Author    : Jack
' Date      : 17-10-2012
' Purpose   : I've been trying to get this to work but I'm finding some bad characters in the xml file
' I can't seem to find any nodes   the length is always 0??????????? JED
'
' I found the bad character and deleted that line from the xml. Then saved that xml to
' my c drive as
'   C:\Documents and Settings\Garay\My Documents\DenmarkBank.xml
'
' I added code to read that file and use it to do the exchange rate extraction.
'
' ***** Bottom line   -- the bad character prevented working the DOM and the xml
'
'
' From a learning view, this is sending an XMLHTTP request and getting a Response and
' using the DOMDocument amd related objects to extract the required data
' from the xml response. This approach seems cleaner and more consistent than using
' vba functions to search and parse strings within the response.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency:
' **********This requires a reference to Microsoft XML, v6.0 **************
'--------------------------------------------------------------------------
'
Sub DemarksNationalBank_ExchangeRate()
 
    Dim arr(34, 1) As String
    Dim Resp As New DOMDocument
    Dim Req As New XMLHTTP
    Dim objNodeList As IXMLDOMNodeList
    Dim objNode As IXMLDOMNode
    Dim objAttribute As IXMLDOMAttribute
    Dim objChildNodes As IXMLDOMNodeList
    Dim mCurrency As String
    Dim mRate As String
    Dim mTime As String
    Dim mDesc As String
    Dim x As Integer
    '
    ' Added this code to get my copy of the xml without the bad character *****
    '
    Dim fso As FileSystemObject
    Dim MyFile As TextStream
    Dim MyXML As String
    Set fso = New FileSystemObject
    Set MyFile = fso.OpenTextFile("C:\Documents and Settings\Garay\My Documents\DenmarkBank.xml", ForReading, False)
    MyXML = MyFile.ReadAll
    Dim i As Integer
 
    On Error GoTo DemarksNationalBank_ExchangeRate_Error
 
    Req.Open "Get", "htt p://ww w.nationalbanken.dk/dndk/valuta.nsf/valuta.xml", False
    Req.send
 
    While Req.Status <> 200
        Debug.Print "waiting for response*** info"
        DoEvents
    Wend
 
    Resp.loadXML MyXML  'Req.responseText '''had to use my own file ****
 
    ' uncomment the next line to see the response
    Debug.Print "XML Response is " & vbCrLf & "~~~~~~~~~~~~~~~~~" & vbCrLf & Req.responseText
 
    x = Resp.getElementsByTagName("dailyrates").Length    'how many nodes
    ' Debug.Print "The number of these nodes is   " & x
 
    Set objNodeList = Resp.getElementsByTagName("dailyrates")
    For Each objNode In objNodeList
        If (objNode.Attributes.Length > 0) Then
            ' Get the "time" attribute for the date to which these exchange rates apply
            Set objAttribute = objNode.Attributes.getNamedItem("id")
            If (Not objAttribute Is Nothing) Then
                Debug.Print "Exchange Rates from DenmarksNational Bank   as at  " & objAttribute.text & vbCrLf _
                            & vbCrLf & "**Read these as 1  of these units = xx DKK" & vbCrLf
                mTime = objAttribute.text
            End If
        End If
    Next
    '----------
 
 
    Set objNodeList = Resp.getElementsByTagName("currency")
 
    For Each objNode In objNodeList
        If (objNode.Attributes.Length > 0) Then
            ' Get the "desc" attribute for these exchange rates
            Set objAttribute = objNode.Attributes.getNamedItem("desc")
            If (Not objAttribute Is Nothing) Then
                mDesc = objAttribute.text
            End If
            ' Get the "code" attribute
            Set objAttribute = objNode.Attributes.getNamedItem("code")
            If (Not objAttribute Is Nothing) Then
                mCurrency = objAttribute.text
                arr(i, 0) = mCurrency
 
            End If
            ' Get the associated "rate" attribute
            Set objAttribute = objNode.Attributes.getNamedItem("rate")
            If (Not objAttribute Is Nothing) Then
                mRate = objAttribute.text
                arr(i, 1) = mRate
                i = i + 1
            End If
            'Put the data in my variables for display
            If mCurrency > " " And mRate > " " Then
                Debug.Print mTime & "   " & mCurrency & "  " & mDesc & "  " & mRate
            End If
        End If
    Next objNode
    'Debug.Print "filled array"
 
    On Error GoTo 0
    Exit Sub
 
DemarksNationalBank_ExchangeRate_Error:
 
    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure DemarksNationalBank_ExchangeRate of Module xmlhttp_Etc"
 
End Sub


Update: Since the post, I learned of ParseError. I used the original code again with an error routine and got this message.



Here's the ParseError cheking code

Code: 
 .....
 Resp.loadXML Req.responseText   '''had to use my own file ****  MyXML
 If Resp.parseError <> 0 Then
 MsgBox "There was a Parse error " & Resp.parseError.errorCode _
    & "  " & Resp.parseError.reason _
    & "  " & Resp.parseError.srcText
 
 Debug.Print "There was a Parse error " & Resp.parseError.errorCode _
    & "  " & Resp.parseError.reason _
    & "  " & Resp.parseError.srcText
 
 End If
.....
Click to expand...
 
Also, I'm not converting from a currency to DKK, but vice versa. I have a bound [Rate] field in my estimate form, controlled by a combobox:
Code: 
Private Sub Currency_AfterUpdate()
Dim objXML As MSXML2.DOMDocument
Dim EUR_Rate As Variant
Dim USD_Rate As Variant
    'Create the DomDocument Object
    Set objXML = CreateObject("MSXML2.DOMDocument")
    'Load entire Document before moving on
    objXML.async = False
    'Don't Validate
    objXML.validateOnParse = False
    objXML.Load _
("htt p://ww w.nationalbanken.dk/dndk/valuta.nsf/valuta.xml")
EUR_Rate = objXML.documentElement.childNodes(0).childNodes(0).Attributes.getNamedItem("rate").Text
USD_Rate = objXML.documentElement.childNodes(0).childNodes(1).Attributes.getNamedItem("rate").Text
If Me.Currency = "EUR" Then
Rate = EUR_Rate
    Rate.Enabled = True
ElseIf Me.Currency = "USD" Then
Rate = USD_Rate
    Rate.Enabled = True
Else
Rate = 100
    Rate.Enabled = False
End If
Me.Refresh
End Sub

All my estimate prices are from the database given in DKK, the calculations are made in the estimate only; ex.: =[TotalJobPrice]*100/[Rate]

rustersen said:
I'm afraid I never really found any good tutorial, I used snippets of code from misc. webpages.

I did read a bit on htt p://ww w.w3schools.com/dom/dom_httprequest.asp
to get a better understanding, but my final code was made up of different sites.

For a very long time I believed SelectSingleNode would be my approach, but I never got it working. (Let me just add I am still a VBA novice, and I have no xml experience whatsoever)

I think this page got me started on childnodes: htt p://ww w.devx.com/getHelpOn/10MinuteSolution/20382

Also, this helped me on loading the xml file:
htt p://ww w.automateexcel.com/2005/06/12/what_is_a_domdocument/

I'm sorry I can't be more precise, I should have been better at saving my links! :)
Click to expand...
 
Thanks for those links. I will look at them and add them to my Favorites.
It is surprising that there really are no tutorials on the DOMDocument and xml etc.

I'd like to find something fairly straightforward. So if anyone knows of sites, please identify same.
Thanks.
 
You must log in or register to reply here.

Similar threads

N
Solved Caching recordset in MS Access VBA
2
Replies
26
Views
1,659
tvanstiphout
T
N
I need to check 10 major currencies using the VBA below
Replies
4
Views
343
arnelgp
arnelgp
J
Solved Insert xml data from responsetext to table in access with VBA
Replies
14
Views
1,506
john_gringo
J
S
VBA Neophyte: How to Save Parsed JSON with indeterminate number of array values
Replies
4
Views
2,084
seanst8579
S
D
Question MS Access 2010 VBA - Upload to SharePoint 2013 Doc Lib
Replies
0
Views
1,942
davestuart
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom