VBA Snippets

XML_ChildNodeToListBox

Public Sub XML_ChildNodeToListBox(ByVal objListBox As Control, _
                                  ByVal iNodeNo As Integer)
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 gobjDOMDocument.documentElement.childNodes.Length - 1
sfundgroup = gobjDOMDocument.documentElement.childNodes(lfundcount).childNodes(iNodeNo).Text
If sfundgroup <> sfundgroup_previous Then
objListBox.AddItem "Group " & sfundgroup
End If
sfundgroup_previous = sfundgroup
Next lfundcount

Exit Sub
AnError:
Call Error_Handle(Err.Number & " - " & Err.Description, "XML_ChildNodeToListBox")
End Sub

XML_Connect

Public Sub XML_Connect(ByVal sFullPath As String)
Set gobjDOMDocument = New MSXML2.DOMDocument
gobjDOMDocument.Load (sFullPath)
End Sub

XML_FileToArray

Public Function XML_FileToArray(Optional ByVal iNodeFilterNo As Integer = -1, _
                                Optional ByVal sNodeFilterText As String = "") _
                                As Variant
Dim vaTemporary As Variant
Dim larraycount As Long
Dim lrowtotal As Long
Dim lrowno As Long
Dim lcolumntotal As Long
Dim lcolumnno As Long

'http://support.microsoft.com/kb/253732

   On Error GoTo AnError
   
   lrowtotal = objDOMDocument.documentElement.childNodes.Length
   lcolumntotal = objDOMDocument.documentElement.childNodes(0).childNodes.Length
   
   ReDim vaTemporary(lcolumntotal - 1, lrowtotal - 1)
   
   For lrowno = 0 To lrowtotal - 1
      If sNodeFilterText = "ALL" Or _
         sNodeFilterText = objDOMDocument.documentElement.childNodes(lrowno).childNodes(iNodeFilterNo).Text Then
             
         For lcolumnno = 0 To lcolumntotal - 1
            vaTemporary(lcolumnno, larraycount) = objDOMDocument.documentElement.childNodes(lrowno).childNodes(lcolumnno).Text
         Next lcolumnno
         
         larraycount = larraycount + 1
      End If
   Next lrowno
   
   ReDim Preserve vaTemporary(lcolumntotal - 1, larraycount - 1)
   
   XML_FileToArray = vaTemporary
   Exit Function

AnError:
   Call Error_Handle(Err.Number & " - " & Err.Description, "XML_FileToArray")
End Function