many .xml files to excel - messy source code. need class module help!

[taleye]

Hi there

Im trying to extract information from many very messy xml source codes. After some tampering with the data in word I paste the data into excel and in column A I have between 10 - 70 rows containing data

The xml data contains tasks whish employees must perform and what tools and quantity of they will require for each task

Each cell in column A contains all the information I need to extract for 1 task. This task may contain 1 tool / consumable / safety equipment, or it may contain many.

Im trying to extract the data so that I can create a list of all the tools and equipment that is needed for each task.

One cell in A contains the below extract


ib miid="BOG00023" doc-type="TASK" maint-item-desc="Anti-Roll Bar" doc-name="BOG00023_TASK_H" doc-title="Anti-Roll Bar - Replace" task-code="H" part-number="3EER400002-3042" lock-counter="19" id-document="6981" id-document-version="31931" doc-version="2" pmt-version="11"><TASK>
<APPLICABLE-CARS>A, B, C, D.</APPLICABLE-CARS><EQUIPMENT-CONDITIONS>
Drain and isolate air suspension.</PARA></EQUIPMENT-CONDITIONS><MAINT-EQUIPMENT><SPECIAL-TOOLS><EQUIPMENT-ITEM>
3EER400007-5156</PART-NUMBER><QUANTITY>1.</QUANTITY></EQUIPMENT-ITEM></SPECIAL-TOOLS><TEST-EQUIPMENT><EQUIPMENT-ITEM></EQUIPMENT-ITEM></TEST-EQUIPMENT><SUPPLIES><EQUIPMENT-ITEM>
3EER400002-3042</PART-NUMBER><QUANTITY>1.</QUANTITY></EQUIPMENT-ITEM><EQUIPMENT-ITEM>
3EER400005-5819</PART-NUMBER><QUANTITY>4.</QUANTITY></EQUIPMENT-ITEM><EQUIPMENT-ITEM>
3EER400005-5818</PART-NUMBER><QUANTITY>4.</QUANTITY></EQUIPMENT-ITEM><EQUIPMENT-ITEM>
3EER400002-3507</PART-NUMBER><QUANTITY>2.</QUANTITY></EQUIPMENT-ITEM></SUPPLIES><SAFETY-EQUIPMENT><EQUIPMENT-ITEM></EQUIPMENT-ITEM></SAFETY-EQUIPMENT><CONSUMABLES><EQUIPMENT-ITEM></EQUIPMENT-ITEM></CONSUMABLES></MAINT-EQUIPMENT></PRE-TASK><</P>

As an example you can see there are a number of equipment items each with their own part number and quantity required.

I thought the best way to tackle this would be to use Class Modules but I am VERY new to these, I have so far managed to get the module to print the headings onto a workbook and then it prints below values I assigned in the script, it does not touch the pasted .xml data at all.

Here is my problem, I dont know how to assign each item in the .xml data to my fields within the script.
After that I dont know how to print each item on another worksheet on a new row and then to say go to the next row on the .xml data sheet and repeat the above process.

I will paste my current code below. As you can see it doesn't touch the .xml data as I don't know how to make it break the data down.

Class Module Named Decrypt

Option Explicit
'Declare private variables accessible only from within this class
Private m_Task As String
Private m_TaskName As String
Private m_TaskRef As String
Private m_TaskDesc As String
Private m_TaskCode As String
Private m_PartNo As String
Private m_ApplicableCars As String
Private m_EquipCond As String
Private m_SpecTools As String
Private m_TestEquip As String
Private m_Supplies As String
Private m_SafetyEquip As String
Private m_Consumables As String
Public Property Get Task() As String
Task = m_Task
End Property
Public Property Let Task(ByVal aNewValue As String)
If aNewValue <> "" Then m_Task = aNewValue
End Property
Public Property Get TaskName() As String
TaskName = m_TaskName
End Property
Public Property Let TaskName(ByVal bNewValue As String)
If bNewValue <> "" Then m_TaskName = bNewValue
End Property
Public Property Get TaskDesc() As String
TaskDesc = m_TaskDesc
End Property
Public Property Let TaskDesc(ByVal dNewValue As String)
If dNewValue <> "" Then m_TaskDesc = dNewValue
End Property
Public Property Get TaskCode() As String
TaskCode = m_TaskCode
End Property
Public Property Let TaskCode(ByVal eNewValue As String)
If eNewValue <> "" Then m_TaskCode = eNewValue
End Property
Public Property Get PartNo() As String
PartNo = m_PartNo
End Property
Public Property Let PartNo(ByVal fNewValue As String)
If fNewValue <> "" Then m_PartNo = fNewValue
End Property
Public Property Get ApplicableCars() As String
ApplicableCars = m_ApplicableCars
End Property
Public Property Let ApplicableCars(ByVal gNewValue As String)
If gNewValue <> "" Then m_ApplicableCars = gNewValue
End Property
Public Property Get EquipCond() As String
EquipCond = m_EquipCond
End Property
Public Property Let EquipCond(ByVal hNewValue As String)
If hNewValue <> "" Then m_EquipCond = hNewValue
End Property
Public Property Get SpecTools() As String
SpecTools = m_SpecTools
End Property
Public Property Let SpecTools(ByVal iNewValue As String)
If iNewValue <> "" Then m_SpecTools = iNewValue
End Property
Public Property Get TestEquip() As String
TestEquip = m_TestEquip
End Property
Public Property Let TestEquip(ByVal jNewValue As String)
If jNewValue <> "" Then m_TestEquip = jNewValue
End Property
Public Property Get Supplies() As String
Supplies = m_Supplies
End Property
Public Property Let Supplies(ByVal kNewValue As String)
If kNewValue <> "" Then m_Supplies = kNewValue
End Property
Public Property Get SafetyEquip() As String
SafetyEquip = m_SafetyEquip
End Property
Public Property Let SafetyEquip(ByVal lNewValue As String)
If lNewValue <> "" Then m_SafetyEquip = lNewValue
End Property
Public Property Get Consumables() As String
Consumables = m_Consumables
End Property
Public Property Let Consumables(ByVal mNewValue As String)
If mNewValue <> "" Then m_Consumables = mNewValue
End Property
Public Property Get TaskRef() As String
TaskRef = m_TaskRef
End Property
Public Property Let TaskRef(ByVal nNewValue As String)
If nNewValue <> "" Then m_TaskRef = nNewValue
End Property
Private Sub Class_Initialize()
'Initialise variables once the class has been instantiated
m_Task = ""
m_TaskName = ""
m_TaskRef = ""
m_TaskDesc = ""
m_TaskCode = ""
m_PartNo = ""
m_ApplicableCars = ""
m_EquipCond = ""
m_SpecTools = ""
m_TestEquip = ""
m_Supplies = ""
m_SafetyEquip = ""
m_Consumables = ""
End Sub
Public Sub SaveDecryption()
Dim wb As Workbook, sht As Worksheet, shts As Worksheet
Set wb = Workbooks.Add
Set sht = wb.Worksheets.Add
sht.Name = Me.ApplicableCars
 
Application.DisplayAlerts = False
For Each shts In wb.Sheets
If shts.Name <> CStr(Me.ApplicableCars) Then shts.Delete
Next
Application.DisplayAlerts = True
'Now add data to the worksheet
With sht
.Cells(1, 1) = "Task"
.Cells(1, 2) = "Task Name"
.Cells(1, 3) = "Task Ref"
.Cells(1, 4) = "Task Description"
.Cells(1, 5) = "Task Code"
.Cells(1, 6) = "Part Number"
.Cells(1, 7) = "Applicable Cars"
.Cells(1, 8) = "Equipment Conditions"
.Cells(1, 9) = "Special Tools"
.Cells(1, 10) = "Test Equipment"
.Cells(1, 11) = "Supplies"
.Cells(1, 12) = "Safety Equipment"
.Cells(1, 13) = "Consumables"
.Cells(2, 1) = Me.Task
.Cells(2, 2) = Me.TaskName
.Cells(2, 3) = Me.TaskRef
.Cells(2, 4) = Me.TaskDesc
.Cells(2, 5) = Me.TaskCode
.Cells(2, 6) = Me.PartNo
.Cells(2, 7) = Me.ApplicableCars
.Cells(2, 8) = Me.EquipCond
.Cells(2, 9) = Me.SpecTools
.Cells(2, 10) = Me.TestEquip
.Cells(2, 11) = Me.Supplies
.Cells(2, 12) = Me.SafetyEquip
.Cells(2, 13) = Me.Consumables
 
End With
End Sub

Standard Module1

Option Explicit
Sub compileData()
Dim dataDecrypt As New Decrypt
dataDecrypt.Task = "None"
dataDecrypt.TaskName = "None"
dataDecrypt.TaskRef = "None"
dataDecrypt.TaskDesc = "None"
dataDecrypt.TaskCode = "None"
dataDecrypt.PartNo = "None"
dataDecrypt.ApplicableCars = "None"
dataDecrypt.EquipCond = "None"
dataDecrypt.SpecTools = "None"
dataDecrypt.TestEquip = "None"
dataDecrypt.Supplies = "None"
dataDecrypt.SafetyEquip = "None"
dataDecrypt.Consumables = "None"
dataDecrypt.SaveDecryption
Set dataDecrypt = Nothing
End Sub

Any assistance at all with this or links to pages where I may get more information would be greatly appreciated, as I said before this is my first ever attempt of working with Class Modules - although hopefully not my last if I can get my head around them!

 

[taleye]

How can I paste the .xml data extract onto this board without it removing the html brackets?