read from xml file into a word document table

[barnard90]

I have an xml file ( C:\Emp.Xml) with employee information in it
It has data with empoyee_id , employee_name, location and salary information
These are the columns and the data is in xml format
Emp_id, Emp_name, Loc, Sal

The number of records in the xml file is varying

I would like to read the xml file and populate the value into a table in a word document
The table row size is dynamically changing based on the number of records in the XML file
Could some one please suggest a VBA macro as to how I can populate XML data into a Word document table

 

[CautionMP]

barnard90,
You can open and parse an XML file pretty easily using an ADO Recordset. Below is a rough concept, I created [tt]C:\Emp.Xml[/tt] using ADO so I didn't have any compatibility issues, you may get some errors you will need to handle if your using a third party XML file.

Sub testrst()
  Dim rst As Object
  Dim objDT As clsDataTable
  
  Set rst = CreateObject("ADODB.Recordset")
  rst.Open "C:\Emp.Xml"
  
  Set objDT = New clsDataTable
  
  objDT.CopyFromRecordset rst, ActiveDocument.Range
  
  Set objDT = Nothing
  rst.Close
  Set rst = Nothing
End Sub

Private mblnWriteHeader As Boolean

Private Sub Class_Initialize()
  mblnWriteHeader = True
End Sub

Public Function CopyFromRecordset(Recordset As Object, Range As Range)
  Dim tblNew As Table, rowNew As Row
  Dim lngColumn As Long, lngRow As Long
  If TypeName(Recordset) <> "Recordset" Then
    Exit Function
  End If
  If Recordset.State = 0 Then
    '0 should be closed for both ADO & DAO
    Exit Function
  End If
  lngColumn = Recordset.Fields.Count
  Set tblNew = ThisDocument.Tables.Add(Range, 1, lngColumn)
  Set rowNew = tblNew.Rows(1)
  For lngColumn = 1 To lngColumn
    rowNew.Cells(lngColumn).Range.Text = Recordset.Fields(lngColumn - 1).Name
  Next lngColumn

  While Not Recordset.EOF
    Set rowNew = tblNew.Rows.Add
    For lngColumn = 1 To Recordset.Fields.Count
      rowNew.Cells(lngColumn).Range.Text = Recordset.Fields(lngColumn - 1)
    Next lngColumn
    Recordset.MoveNext
  Wend
  
  Set rowNew = Nothing
  Set tblNew = Nothing
End Function

Public Property Get WriteHeader() As Boolean
  WriteHeader = mblnWriteHeader
End Property

Public Property Let WriteHeader(ByVal blnNewValue As Boolean)
  mblnWriteHeader = blnNewValue
End Property

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)