1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248
| Public Sub CreerDocumentRMXML()
Dim xmlDoc As Object
Dim Findings As Object
Dim Findingnb As Object
Dim Titre1 As Object
Dim Titre2 As Object
Dim Titre3 As Object
Dim Titre8 As Object
Dim FindingX As Object
Dim MisuseX As Object
Dim MissingPartX As Object
Dim CelluleReferenceGF As Variant
Dim CelluleReferenceDPR As Variant
Dim DerniereLigneNonVide As Integer
Dim DerniereCelluleDefaultNonVide As Integer
Dim DerniereCelluleGFNonVide As Integer
Dim i, j, k, X, L, b As Byte
Dim a As String
Dim Reference(12) As String
Dim Chemin2 As String
Dim AnnonceFindingnbElement(12) As Variant
j = 4
i = 21
L = 1
k = 21
X = 1
a = ""
b = 0
Set xmlDoc = CreateObject("Microsoft.XMLDOM")
Set Node = xmlDoc.CreateProcessingInstruction("xml", "version=""1.0"" encoding=""ISO-8859-1""")
xmlDoc.appendChild Node
Set Node = Nothing
Set ProjetXML = xmlDoc.CreateElement("ProjetXML")
xmlDoc.appendChild ProjetXML
Set Titre3 = xmlDoc.CreateElement("Titre3")
ProjetXML.appendChild Titre3
Titre3.Text = " PN : SN : WO : "
Set Titre4 = xmlDoc.CreateElement("Titre4")
ProjetXML.appendChild Titre4
Titre4.Text = " "
Set Titre5 = xmlDoc.CreateElement("Titre5")
ProjetXML.appendChild Titre5
Titre5.Text = " "
Set Titre6 = xmlDoc.CreateElement("Titre6")
ProjetXML.appendChild Titre6
Titre6.Text = " ADDITIONAL PARTS MAY BE REQUIRED AFTER ACCEPTANCE TEST COMPLETION. "
Set Titre9 = xmlDoc.CreateElement("Titre9")
ProjetXML.appendChild Titre9
Titre9.Text = " "
Set Titre10 = xmlDoc.CreateElement("Titre10")
ProjetXML.appendChild Titre10
Titre10.Text = " "
DerniereCelluleDefaultNonVide = Range("G300").End(xlUp).Row
For i = 21 To DerniereCelluleDefaultNonVide
If Cells(i, 7) = "Misuse/mishandeling" Then
Set Titre1 = xmlDoc.CreateElement("Titre1")
ProjetXML.appendChild Titre1
Titre1.Text = " MISUSE : YES "
Set Titre7 = xmlDoc.CreateElement("Titre7")
ProjetXML.appendChild Titre7
Titre7.Text = " REASON : "
Exit For
End If
Next i
i = 21
For i = 21 To DerniereCelluleDefaultNonVide
Set MisuseX = Nothing
If Cells(i, 7) = "Misuse/mishandeling" Then
Set MisuseX = xmlDoc.CreateElement("MisuseX")
Titre7.appendChild MisuseX
MisuseX.Text = Cells(i, 7).Offset(0, -4).Text & " | P/N : " & Cells(i, 7).Offset(0, -5).Text & " | Found " & Cells(i, 7).Offset(0, -1).Text
End If
Next i
CelluleReferenceGF = Cells(j, 3)
DerniereCelluleGFNonVide = Range("C15").End(xlUp).Row
Set PlageReferenceGF = Range(Cells(4, 3), Cells(DerniereCelluleGFNonVide, 3))
For Each CelluleReferenceGF In PlageReferenceGF
If CelluleReferenceGF.Text = "PART MISSING" Then
a = "PART MISSING"
b = CelluleReferenceGF.Offset(0, -1).Value
Exit For
End If
Next CelluleReferenceGF
If b <> 0 Then
Set Titre8 = xmlDoc.CreateElement("Titre8")
ProjetXML.appendChild Titre8
Titre8.Text = " MISSING PARTS : YES "
End If
i = 21
For i = 21 To DerniereCelluleDefaultNonVide
Set MissingPartX = Nothing
If Cells(i, 5).Value = b Then
Set MissingPartX = xmlDoc.CreateElement("MissingPartX")
Titre8.appendChild MissingPartX
MissingPartX.Text = Cells(i, 5).Offset(0, -2).Text & " | P/N : " & Cells(i, 5).Offset(0, -3).Text & " | Found " & Cells(i, 5).Offset(0, 1).Text
End If
Next i
Set Titre2 = xmlDoc.CreateElement("Titre2")
ProjetXML.appendChild Titre2
Titre2.Text = " FINDINGS : "
For j = 4 To 15
Set AnnonceFindingnbElement(L) = Nothing
If Cells(j, 3) <> "" And Cells(j, 2).Value <> b Then
Set AnnonceFindingnbElement(L) = xmlDoc.CreateElement("AnnonceFindingnbElement")
ProjetXML.appendChild AnnonceFindingnbElement(L)
AnnonceFindingnbElement(L).Text = "FOR FINDING : " & Cells(j, 3).Text
L = L + 1
End If
Next j
DerniereLigneNonVide = Range("E300").End(xlUp).Row
CelluleReferenceDPR = Cells(k, 5)
Set PlageReference = Range(Cells(21, 5), Cells(DerniereLigneNonVide, 5))
Reference(1) = 1
Reference(2) = 2
Reference(3) = 3
Reference(4) = 4
Reference(5) = 5
Reference(6) = 6
Reference(7) = 7
Reference(8) = 8
Reference(9) = 9
Reference(10) = 10
Reference(11) = 11
Reference(12) = 12
For L = 1 To 5
For Each CelluleReferenceDPR In PlageReference
If CelluleReferenceDPR.Value <> b Then
If CelluleReferenceDPR.Value = Reference(X) Then
Set FindingX = Nothing
Set FindingX = xmlDoc.CreateElement("FindingX")
AnnonceFindingnbElement(L).appendChild FindingX
FindingX.Text = CelluleReferenceDPR.Offset(0, -2).Text & " | P/N : " & CelluleReferenceDPR.Offset(0, -3).Text & " | Found " & CelluleReferenceDPR.Offset(0, 1).Text & " due to " & CelluleReferenceDPR.Offset(0, 2).Text
End If
ElseIf CelluleReferenceDPR.Value = b Then
Reference(X) = Reference(X + 1)
If CelluleReferenceDPR.Value = Reference(X) Then
Set FindingX = Nothing
Set FindingX = xmlDoc.CreateElement("FindingX")
AnnonceFindingnbElement(L).appendChild FindingX
FindingX.Text = CelluleReferenceDPR.Offset(0, -2).Text & " | P/N : " & CelluleReferenceDPR.Offset(0, -3).Text & " | Found " & CelluleReferenceDPR.Offset(0, 1).Text & " due to " & CelluleReferenceDPR.Offset(0, 2).Text
End If
End If
Next CelluleReferenceDPR
X = X + 1
Next L
Chemin2 = ThisWorkbook.Path & "\MCO Annexe MWS.xml"
xmlDoc.Save Chemin2
Set xmlDoc = Nothing
End Sub |
Partager