'Creating a loop in VBS to write the output in XML

I want to pull a file from a program and have that file create itself into XML and then run a preset script. its being run through an excel macro I need this to loop until it reaches a non null value. This is kinda what I have going on, but I can't seem to get this to compile.

With ActiveSheet
    For Each Filename In .Range("A2:A" & GetLastRow("Sheet1"))

    
        Set XML = FSO.CreateTextFile( _
                    Filename:=ThisWorkbook.Path & "\" & "ERP" & ".erp", _
                    Overwrite:=True)
       
        With Filename
            XML.WriteLine ("<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>")
            XML.WriteLine ("    <ErpExchange xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">")
            XML.WriteLine ("        <Orders>")
                    Do While BlankFound = False
                      x = x + 1
                    
                                         
                    XML.WriteLine ("            <ErpOrder>")
                    XML.WriteLine ("                <ImportType>NewOrder</ImportType>")
                    XML.WriteLine ("                <OrderNumber>" & .Offset(x - 1, 1).Value & "</OrderNumber>")
                    XML.WriteLine ("                <TargetDate>" & Format(.Offset(x - 1, 2).Value, "yyyy-mm-ddThh:mm:ss") & "</TargetDate>")
                    XML.WriteLine ("                <ProductionStrategy>TargetDateOrder</ProductionStrategy>")
                    XML.WriteLine ("                <Automatic>False</Automatic>")
                    XML.WriteLine ("                <Parts>")
                    XML.WriteLine ("                    <ErpPart>")
                    XML.WriteLine ("                        <BysoftCode>" & .Offset(x - 1, 3).Value & "</BysoftCode>")
                    XML.WriteLine ("                        <Debit>" & .Offset(x - 1, 4).Value & "</Debit>")
                    XML.WriteLine ("                        <Measure>Inch</Measure>")
                    XML.WriteLine ("                    </ErpPart>")
                    XML.WriteLine ("                </Parts>")
                    XML.WriteLine ("            </ErpOrder>")
                    If Cells(x, "D").Value = "" Then
                        BlankFound = False
                    ElseIf Cells(x, "D").Value = "Ture" Then
                        BlankFound = True
                    If BlankFound = True Then Exit Do
                    Loop
                        
                        
              XML.WriteLine ("        </Orders>")
            XML.WriteLine ("    </ErpExchange>")
        End With
       
        XML.Close
    Next Filename
End With

Solution 1:^[1]

Consider use a template with placeholders that you can replace with values.

Option Explicit

Sub macro1()

    ' XML Template
    Const TMPL = "<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>" & vbCrLf & _
    "<ErpExchange xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">" & vbCrLf & _
    "    <Orders>" & vbCrLf & _
    "        <ErpOrder>" & vbCrLf & _
    "            <ImportType>NewOrder</ImportType>" & vbCrLf & _
    "            <OrderNumber>{NO}</OrderNumber>" & vbCrLf & _
    "            <TargetDate>{DATE}</TargetDate>" & vbCrLf & _
    "            <ProductionStrategy>TargetDateOrder</ProductionStrategy>" & vbCrLf & _
    "            <Automatic>False</Automatic>" & vbCrLf & _
    "            <Parts>" & vbCrLf & _
    "                <ErpPart>" & vbCrLf & _
    "                    <BysoftCode>{CODE}</BysoftCode>" & vbCrLf & _
    "                    <Debit>{DEBIT}</Debit>" & vbCrLf & _
    "                    <Measure>Inch</Measure>" & vbCrLf & _
    "                </ErpPart>" & vbCrLf & _
    "            </Parts>" & vbCrLf & _
    "        </ErpOrder>" & vbCrLf & _
    "    </Orders>" & vbCrLf & _
    "</ErpExchange>"
    
    Dim ws As Worksheet, cell As Range, XML
    Dim s As String, FSO As Object
    
    Set ws = ActiveSheet
    Set FSO = CreateObject("Scripting.FileSystemObject")

    ' scan sheet
    For Each cell In ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)

        With cell
            If .Offset(, 4).Value = "Ture" Then Exit For
        
            s = TMPL
            s = Replace(s, "{NO}", .Offset(, 1).Value) ' order number
            s = Replace(s, "{DATE}", Format(.Offset(, 2).Value, "yyyy-mm-ddThh:mm:ss")) ' target date
            s = Replace(s, "{CODE}", .Offset(, 3).Value) ' BysoftCode
            s = Replace(s, "{DEBIT}", .Offset(, 4).Value) ' Debit
        End With
        
        Set XML = FSO.CreateTextFile( _
                    filename:=ThisWorkbook.Path & "\" & "ERP" & ".erp", _
                    Overwrite:=True)
        
        XML.Write s
        XML.Close
        
        ' process xml
        MsgBox s
    Next
End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	CDP1802

'Creating a loop in VBS to write the output in XML

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]