• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

vba code to parse xml file

looking for some vba coding to parse thru an xml file, need to extract : the following fields:Vendor FirmID and Name,UserID,lastname,firstname,email,along with the credits and credits detail information.
New to XML and lost as where to begin
  • 2
  • 2
1 Solution
Fabrice LambertFabrice LambertCommented:

Wich datas do you need from credits and creditDetails ?
I can offer a solution but you need to elaborate.
ste5anSenior DeveloperCommented:
You need to set a reference to Microsoft XML. E.g.

Option Compare Database
Option Explicit

Public Sub TestCountGetElementsByTagName()

  'Using reference to MSXML2.
  Dim document As MSXML2.DOMDocument60
  Dim credits As MSXML2.IXMLDOMNodeList
  Dim credit As MSXML2.IXMLDOMNode
  Dim vendors As MSXML2.IXMLDOMNodeList
  Dim vendor As MSXML2.IXMLDOMNode
  Dim user As MSXML2.IXMLDOMNode
  Dim XPath As String
  Set document = New MSXML2.DOMDocument60
  document.SetProperty "SelectionLanguage", "XPath"
  document.Load "C:\Temp\CEM_PLI2.xml"
  XPath = "//CEM/Vendor"
  Set vendors = document.documentElement.selectNodes(XPath)
  For Each vendor In vendors
    Debug.Print vendor.baseName
    Debug.Print vendor.Attributes.getNamedItem("Name").Text
    Debug.Print vendor.Attributes.getNamedItem("VendorFirmID").Text
    Set user = vendor.selectSingleNode("User")
    Debug.Print user.Attributes.getNamedItem("id").Text
    Debug.Print user.selectSingleNode("LastName").Text
    Set credits = user.selectNodes("Credits")
    For Each credit In credits
      Debug.Print Chr(9) & credit.Attributes.getNamedItem("TotalCredits").Text
      Debug.Print Chr(9) & credit.Attributes.getNamedItem("Item_SK").Text
    Next credit
  Next vendor
  Set credits = Nothing
  Set credit = Nothing
  Set user = Nothing
  Set vendor = Nothing
  Set vendors = Nothing
  Set document = Nothing
End Sub

Open in new window

Fabrice LambertFabrice LambertCommented:
I wouldn't set a reference, as it is a major source of troubles, but use late binding instead.
Plus you're retrieving data from the 1st user only, and missing credit détails.

You'll find an object oriented approach in the workbook attached below, see the test() procedure in the test module.
It isn't completed yet tho, as I'm still waiting for your answer about credit and credit details data.
ste5anSenior DeveloperCommented:
It was just a sample, not ment to be complete.. and while development I use always early binding. IntelliSense, you know ;)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now