1

I built a code that creates a .xml file for each row, for example, the below spreadsheet
Example: Rows to transform

The following code works fine except for one case when ResetDate has the same value for more than 1 row.

The VBA code (I'm not an expert) 

Sub testXLStoXML()
 sTemplateXML = _
        "<?xml version='1.0'?>" + vbNewLine + _
        "<E>" + vbNewLine + _
        "   <ResetDate>" + vbNewLine + _
        "   </ResetDate>" + vbNewLine + _
        "   <ValueDate>" + vbNewLine + _
        "   </ValueDate>" + vbNewLine + _
        "   <MaturityD>" + vbNewLine + _
        "   </MaturityD>" + vbNewLine + _
        "   <Rate>" + vbNewLine + _
        "   </Rate>" + vbNewLine + _
        "   <Quantity>" + vbNewLine + _
        "   </Quantity>" + vbNewLine + _
        "   <ID>" + vbNewLine + _
        "   </ID>" + vbNewLine + _
        "</E>" + vbNewLine

 Set doc = CreateObject("MSXML2.DOMDocument")
 doc.async = False
 doc.validateOnParse = False
 doc.resolveExternals = False

 With ActiveWorkbook.Worksheets(1)
  lLastRow = .UsedRange.Rows.Count

  For lRow = 2 To lLastRow
   sFile = Format(.Cells(lRow, 1).Value, "DD-MMM-YY")
   sBirthdate = Format(.Cells(lRow, 2).Value, "DD-MMM-YY")
   sAmount = Format(.Cells(lRow, 3).Value, "DD-MMM-YY")
   sRate = .Cells(lRow, 4).Value
   sQuantity = .Cells(lRow, 5).Value
   sID = .Cells(lRow, 6).Value
   doc.LoadXML sTemplateXML
   doc.getElementsByTagName("ResetDate")(0).appendChild doc.createTextNode(ResetDate)
   doc.getElementsByTagName("ValueDate")(0).appendChild doc.createTextNode(ValueDate)
   doc.getElementsByTagName("MaturityD")(0).appendChild doc.createTextNode(MaturityD)
  doc.getElementsByTagName("Rate")(0).appendChild doc.createTextNode(sRate)
  doc.getElementsByTagName("Quantity")(0).appendChild doc.createTextNode(sQuantity)
  doc.getElementsByTagName("ID")(0).appendChild doc.createTextNode(sID)
   doc.Save sFile
  Next

 End With
End Sub

Output 10 files, output expected 11 files

As you can see I get as output just 1 file for the date:

enter image description here

Any suggestions? Thanks in advance

Improve this question
6
  • 1
    On which line does the error occur? Also since you are saving by the reset date, don't you expect an error if you are saving with an existing file name? Commented Jul 10, 2019 at 14:38
  • Hi, @AAA I don't see any error, the VBA runs without issues. I can't see any message like overwriting/replacing an existing file, following your question now my question would be: " can I save the files, not by ResetDate? saving them like file_1, file_2, file_3,..., file_n?" thanks for your message. Commented Jul 10, 2019 at 14:44
  • 1
    As mentioned by @AAA, You're using the following code to determine your filename sFile = Format(.Cells(lRow, 1).Value, "DD-MMM-YY"), which references column 1, in which your `ResetDate1 is contained, hence two files will have the same name :0) Commented Jul 10, 2019 at 14:59
  • Hello @AAA sorry for the late reply, yesterday I didn't time to look at it. It's working fine now, fixed, thanks for your interest and your time. Regards! Commented Jul 11, 2019 at 7:50
  • @LorenzoCastagno, if it worked for you, you should upvote and mark as accepted answer. Commented Jul 11, 2019 at 10:54

1 Answer 1

Reset to default
1

Delete doc.Save sFile and replace with the following code:

Dim x as Long
x = Application.CountIf(.Range("A2:A" & lrow), .Cells(lRow, 1))
If  x > 1 Then doc.Save sFile & "_" & x Else doc.Save sFile

So your amended code would be:

Sub testXLStoXML()
Dim x as Long
sTemplateXML = _
    "<?xml version='1.0'?>" + vbNewLine + _
    "<E>" + vbNewLine + _
    "   <ResetDate>" + vbNewLine + _
    "   </ResetDate>" + vbNewLine + _
    "   <ValueDate>" + vbNewLine + _
    "   </ValueDate>" + vbNewLine + _
    "   <MaturityD>" + vbNewLine + _
    "   </MaturityD>" + vbNewLine + _
    "   <Rate>" + vbNewLine + _
    "   </Rate>" + vbNewLine + _
    "   <Quantity>" + vbNewLine + _
    "   </Quantity>" + vbNewLine + _
    "   <ID>" + vbNewLine + _
    "   </ID>" + vbNewLine + _
    "</E>" + vbNewLine

Set doc = CreateObject("MSXML2.DOMDocument")
doc.async = False
doc.validateOnParse = False
doc.resolveExternals = False

With ActiveWorkbook.Worksheets(1)
    lLastRow = .UsedRange.Rows.Count
    For lRow = 2 To lLastRow
        sFile = Format(.Cells(lRow, 1).Value, "DD-MMM-YY")
        sBirthdate = Format(.Cells(lRow, 2).Value, "DD-MMM-YY")
        sAmount = Format(.Cells(lRow, 3).Value, "DD-MMM-YY")
        sRate = .Cells(lRow, 4).Value
        sQuantity = .Cells(lRow, 5).Value
        sID = .Cells(lRow, 6).Value
        doc.LoadXML sTemplateXML
        doc.getElementsByTagName("ResetDate")(0).appendChild doc.createTextNode(ResetDate)
        doc.getElementsByTagName("ValueDate")(0).appendChild doc.createTextNode(ValueDate)
        doc.getElementsByTagName("MaturityD")(0).appendChild doc.createTextNode(MaturityD)
        doc.getElementsByTagName("Rate")(0).appendChild doc.createTextNode(sRate)
        doc.getElementsByTagName("Quantity")(0).appendChild doc.createTextNode(sQuantity)
        doc.getElementsByTagName("ID")(0).appendChild doc.createTextNode(sID)
        x = Application.CountIf(.Range("A2:A" & lrow), .Cells(lRow, 1))
        If  x > 1 Then doc.Save sFile & "_" & x Else doc.Save sFile
    Next lrow
End With

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.