Logic to create header and detail lines xml file

[Flowcentric]

Hi,

Please assist, I am trying to create the header and detail lines for an xml file from an excel Spreadsheet.
Thanking you in advance for your assistance

Column C is the customer code and part of the header information ( one header line)
Column D is the stock code and part of the detail information(many detail lines)
There is other information in the xml but the looping will be on the Customer and stockcode

Customer Stockcode

Customer Stockcode
MSA206 BSL101
MSA206 BSL103
MSA206 BSL102
MSA201 ESX53
MSA201 ESX531
MSA201 ESX531
MSA201 ESX531
MSA201 ESX532

Herewith my code
"Sub XMLBULK()
Dim Row
Row = 3
Dim var As String
' The customer code must only appear once
Do While Range("C" & Row).Text <> ""
If Range("C" & Row).Text <> Range("C" & Row + 1).Text Then
var = Range("C" & Row).Text
MsgBox var
'Header
Document = Document & "<PostSalesOrdersSCT> "
Document = Document & "<Orders>"
Document = Document & "<OrderHeader>"
Document = Document & "<CustomerPoNumber>" & Range("B" & Row).Text & "</CustomerPoNumber>"
Document = Document & "<SourceWarehouse>" & Range("D1").Text & "</SourceWarehouse>"
Document = Document & "<TargetWarehouse>" & Range("G1").Text & "</TargetWarehouse>"
Document = Document & "<WarehouseName>" & Range("C" & Row).Text & "</WarehouseName>"
Document = Document & "<ShipAddress1>" & Range("H" & Row).Text & "</ShipAddress1>"
Document = Document & "<ShipAddress2>" & Range("J" & Row).Text & "</ShipAddress2>"
Document = Document & "<ShipAddress3>" & Range("K" & Row).Text & "</ShipAddress3>"
Document = Document & "<ShipAddress4>" & Range("L" & Row).Text & "</ShipAddress4>"
Document = Document & "<ShipAddress5>" & Range("M" & Row).Text & "</ShipAddress5>"
Document = Document & "<SalesOrder/>"
Document = Document & "<OrderDate/>"
Document = Document & "</OrderHeader>"
'Each stock code for the customer must appear in the detail section
Dim i As Long
For i = 3 To 20
If var = Range("C" & i).Value Then
'detail

Document = Document & "<OrderDetails>"
Document = Document & "<StockLine>"
Document = Document & "<StockCode>" & Range("D" & Row).Text & "</StockCode>"
Document = Document & "<OrderQty>" & Range("E" & Row).Text & "</OrderQty>"
Document = Document & "<OrderUom/>"
Document = Document & "</StockLine>"
Document = Document & "</OrderDetails>"


End If
Next


Document = Document & "</Orders> "
Document = Document & " </PostSalesOrdersSCT>"


End If
Row = Row + 1
Loop



'Save LogFile


Dim FSO
Dim FSOFile
Set FSO = CreateObject("Scripting.FileSystemObject")
'Set FSOFile = FSO.CreateTextFile("\\Tevdbn-app-01\syspro7\DFM\CompTest\SCT\2.xml")
Set FSOFile = FSO.CreateTextFile("\\tevdbn-app-01\syspro7\DFM\BulkSCT\Inv.txt")


FSOFile.WriteLine (Document)
FSOFile.Close


'Housekeeping



Set FSO = Nothing
Set FSOFile = Nothing
Document = ""
End Sub"

 

[Flowcentric]

sorry my code did not copy correctly

My first loop works correctly. The second loop is where I am have a problem with. I need it to create all lines for each customer

Herewith code
Sub XMLBULK()
Dim Row
Row = 3
Dim var As String


Do While Range("C" & Row).Text <> ""
If Range("C" & Row).Text <> Range("C" & Row + 1).Text Then
var = Range("C" & Row).Text
MsgBox var
'Header
Document = Document & "<PostSalesOrdersSCT> "
Document = Document & "<Orders>"
Document = Document & "<OrderHeader>"
Document = Document & "<CustomerPoNumber>" & Range("B" & Row).Text & "</CustomerPoNumber>"
Document = Document & "<SourceWarehouse>" & Range("D1").Text & "</SourceWarehouse>"
Document = Document & "<TargetWarehouse>" & Range("G1").Text & "</TargetWarehouse>"
Document = Document & "<WarehouseName>" & Range("C" & Row).Text & "</WarehouseName>"
Document = Document & "<ShipAddress1>" & Range("H" & Row).Text & "</ShipAddress1>"
Document = Document & "<ShipAddress2>" & Range("J" & Row).Text & "</ShipAddress2>"
Document = Document & "<ShipAddress3>" & Range("K" & Row).Text & "</ShipAddress3>"
Document = Document & "<ShipAddress4>" & Range("L" & Row).Text & "</ShipAddress4>"
Document = Document & "<ShipAddress5>" & Range("M" & Row).Text & "</ShipAddress5>"
Document = Document & "<SalesOrder/>"
Document = Document & "<OrderDate/>"
Document = Document & "</OrderHeader>"


Dim i As Long
For i = 3 To 20
If var = Range("C" & i + 1).Value Then
'detail





Document = Document & "<OrderDetails>"
Document = Document & "<StockLine>"
Document = Document & "<StockCode>" & Range("D" & Row).Text & "</StockCode>"
Document = Document & "<OrderQty>" & Range("E" & Row).Text & "</OrderQty>"
Document = Document & "<OrderUom/>"
Document = Document & "</StockLine>"
Document = Document & "</OrderDetails>"


End If


Next i



Document = Document & "</Orders> "
Document = Document & " </PostSalesOrdersSCT>"


End If
Row = Row + 1
Loop



'Save LogFile


Dim FSO
Dim FSOFile
Set FSO = CreateObject("Scripting.FileSystemObject")
'Set FSOFile = FSO.CreateTextFile("\\Tevdbn-app-01\syspro7\DFM\CompTest\SCT\2.xml")
Set FSOFile = FSO.CreateTextFile("\\tevdbn-app-01\syspro7\DFM\BulkSCT\Inv.txt")


FSOFile.WriteLine (Document)
FSOFile.Close


'Housekeeping



Set FSO = Nothing
Set FSOFile = Nothing
Document = ""
End Sub