Microsoft XML v6.0

Adding this reference allows you to use the MSXML application programming interfaces from within VBA.

It is possible to treat an XML file as a text file and parse it using traditional methods but is not very efficient.
However because the XML data is structured it is far easier to use an XML parser instead.
There are lots of different type of XML parsers but for the purposes of VBA you will want to use a parser that supports XML Document Object Model (DOM).
An XML parser that supports DOM will take the XML data and expose it via a set of objects that we can program against.

Microsoft XML Parser (MsXML.dll)

This is an XML parser which supports DOM that can be used in VBA to access and manipulate XML data.
(Tools > References)
C:\windows\system32\msxml6.dll

Document Object Model (DOM)

This defines a set of standard commands that parsers should expose to allow you to access XML content.
The DOM represents a treeview of an XML document letting you easily navigate its structure and add, modify or remove elements.
The documentElement is the top level of the tree.
This element has one or more child nodes.

link msdn - aa468547
KB - 304265
KB - 253732

Dim oDOMNodeList As MSXML2.IXMLDOMNodeList 
Dim oXMLDoc As MSXML2.DOMDocument60 
Dim oNode As MSXML2.IXMLDOMNode 

oXMLDoc = New MSXML2.DOMDocument60 
oXMLDoc.LoadXML(oDOMNodeList(1).XML) 
oDOMNodeList(1).SelectSingleNode("--").Text 
oXMLDoc.documentElement.childNodes(1).childNodes(1).Text 

MSXML2.serverXMLHTTP

Dim xmlhttp As New MSXML2.XMLHTTP 
Dim xmlhttp As New MSXML2.XMLHTTP60    'for Microsoft XML, v 6.0  

xmlhttp.Open Method, URL, async(true or false) 
myurl = "http://bettersolutions.com/myurl" 
xmlhttp.Open "GET", myurl, False 
xmlhttp.Send 
MsgBox(xmlhttp.responseText) 

xmlhttp.setRequestHeader "Content-Type", "text/json" 
xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" 
xmlhttp.setRequestHeader "User-Agent", "Mozilla/5.0" 
xmlhttp.setRequestHeader "Authorization", AuthCredentials 

Dim xmlhttp As New MSXML2.XMLHTTP, myurl As String 
myurl = "http://requestb.in/15oxrjh1" 
xmlhttp.Open "POST", myurl, False 
xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" 
xmlhttp.Send "name=codingislove&email=admin@codingislove.com" 
MsgBox (xmlhttp.responseText) 

user = "someusername" 
password = "somepassword" 
xmlhttp.setRequestHeader "Authorization", "Basic " + Base64Encode(" & "myusername" & ":" & "mypassword" & ")" 

Public objDOMDocument As MSXML2.DOMDocument60 

'***********************************************************************************
Public Sub XML_Connect() 
   Set objDOMDocument = New MSXML2.DOMDocument60 
   objDOMDocument.Load (sFULLPATH) 
End Sub 
'***********************************************************************************
Public Sub DisplayData() 
Dim lfundcount As Long 
Dim sfundgroup As String 
Dim sfundgroup_previous As String 
Dim sfundname As String 

   On Error GoTo AnError 
   For lfundcount = 0 To objDOMDocument.documentElement.childNodes.Length - 1 
      sfundgroup = objDOMDocument.documentElement.childNodes(lfundcount).childNodes(0).Text 
      If sfundgroup <> sfundgroup_previous Then 
         frmLoanTradeReport.lsbFundsList.AddItem "Group " & sfundgroup 
      End If 
      sfundgroup_previous = sfundgroup 
   Next lfundcount 
   Exit Sub 

AnError: 

End Sub 
'***********************************************************************************
Public Function FundsInGroup(ByVal sGroupName As String, _ 
                    Optional ByVal bReturnFirstFund As Boolean = False) As String 
Dim lfundcount As Long 
Dim sFundConCat As String 
Dim sfundgroup As String 
Dim sfundname As String 

   On Error GoTo AnError 
   For lfundcount = 0 To objDOMDocument.documentElement.childNodes.Length - 1 
      sfundgroup = objDOMDocument.documentElement.childNodes(lfundcount).childNodes(0).Text 
      sfundname = objDOMDocument.documentElement.childNodes(lfundcount).childNodes(1).Text 
      If sfundgroup = sGroupName Then 
         sFundConCat = sFundConCat & sfundname & ";" 
         If bReturnFirstFund = True Then Exit For 
      End If 
   Next lfundcount 
   
   FundsInGroup = Left(sFundConCat, Len(sFundConCat) - 1) 
   Exit Function 

AnError: 

End Function 
'***********************************************************************************
Public Sub ExportFundList() 

   Call ExportToXML(sFULLPATH, "Fund") 
   
End Sub 
'***********************************************************************************
Public Function ExportToXML(ByVal FullPath As String, _ 
                            ByVal RowName As String) As Boolean 

On Error GoTo AnError 

Dim colIndex As Integer 
Dim rwIndex As Integer 
Dim asCols() As String 
Dim oWorkSheet As Worksheet 
Dim sName As String 
Dim lCols As Long, lRows As Long 
Dim iFileNum As Integer 
Dim i As Long 
Dim j As Long 

   Set oWorkSheet = ThisWorkbook.Worksheets(1) 
   sName = oWorkSheet.Name 
   lCols = oWorkSheet.Columns.Count 
   lRows = oWorkSheet.Rows.Count 
   
   ReDim asCols(lCols) As String 
   
   iFileNum = FreeFile 
   Open FullPath For Output As #iFileNum 
   
   For i = 0 To lCols - 1 
       'Assumes no blank column names
       If Trim(Cells(1, i + 1).Value) = "" Then Exit For 
       asCols(i) = Cells(1, i + 1).Value 
   Next i 
   
   If i = 0 Then GoTo AnError 
   lCols = i 
      
   Print #iFileNum, "<?xml version=""1.0""?>" 
   
   Print #iFileNum, "<FundList>" 
   
   For i = 2 To lRows 
   If Trim(Cells(i, 1).Value) = "" Then Exit For 
   Print #iFileNum, "<" & RowName & ">" 
     
       For j = 1 To lCols 
           
           If Trim(Cells(i, j).Value) <> "" Then 
              Print #iFileNum, "  <" & asCols(j - 1) & ">" & Trim(Cells(i, j).Value) & "</" & asCols(j - 1) & ">" 
           End If 
       Next j 
       Print #iFileNum, "</" & RowName & ">" 
   Next i 
   
   Print #iFileNum, "</FundList>" 
   
   ExportToXML = True 
   
AnError: 
   If iFileNum > 0 Then Close #iFileNum 

End Function