Error while converting excelsheet to XML

Question

Hello i want to convert a large excel sheet to xml files in a specific folder. To do this i have read the question ( Convert rows of spreadsheet to separate XML files ). I try to apply it and adjust it a litle so it will fit to the excel format:

after adjusting the code it looks like this:

Sub xmlPerRow()

 sTemplateXML = _
        "<?xml version='1.0'?>" + vbNewLine + "<data>" + vbNewLine + "<Grai>" + vbNewLine + "</Grai>" + vbNewLine + _
        "   <DayDateOut>" + vbNewLine + "   </DayDateOut>" + vbNewLine + "   <Filler>" + vbNewLine + "   </Filler>" + vbNewLine + _
        "   <FillerCountry>" + vbNewLine + "   </FillerCountry>" + vbNewLine + "   <Retailer>" + vbNewLine + "   </Retailer>" + vbNewLine + _
        "   <RetailerCountry>" + vbNewLine + "   </RetailerCountry>" + vbNewLine + "   <Days>" + vbNewLine + "   </Days>" + vbNewLine + _
        "   <DayBack>" + vbNewLine + "   </DayBack>" + vbNewLine + "   <DateIn>" + vbNewLine + "   </DateIn>" + vbNewLine + _
        "   <BrokenCode>" + vbNewLine + "   </BrokenCode>" + vbNewLine + "   <Broken>" + vbNewLine + "   </Broken>" + vbNewLine + _
        "   <TotalCycles>" + vbNewLine + "   </TotalCycles>" + vbNewLine + "</data>" + 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 = .Cells(lRow, 1).Value & ".xml"
   sGrai = .Cells(lRow, 1).Value
   sDayDateOut = .Cells(lRow, 2).Value
   sFiller = .Cells(lRow, 3).Value
   sRetailer = .Cells(lRow, 4).Value
   sRetailerCountry = .Cells(lRow, 5).Value
   sDays = .Cells(lRow, 6).Value
   sDayBack = .Cells(lRow, 7).Value
   sDateIn = .Cells(lRow, 8).Value
   sBrokenCode = .Cells(lRow, 9).Value
   sBroken = .Cells(lRow, 10).Value
   sTotalCycles = .Cells(lRow, 11).Value

   doc.LoadXML sTemplateXML
   doc.getElementsByTagName("Grai")(0).appendChild doc.createTextNode(sGrai)
   doc.getElementsByTagName("DayDateOut")(0).appendChild doc.createTextNode(sDayDateOut)
   doc.getElementsByTagName("Filler")(0).appendChild doc.createTextNode(sFiller)
   doc.getElementsByTagName("RetailerCountry")(0).appendChild doc.createTextNode(sRetailerCountry)
   doc.getElementsByTagName("Retailer")(0).appendChild doc.createTextNode(sRetailer)
   doc.getElementsByTagName("Days")(0).appendChild doc.createTextNode(sDays)
   doc.getElementsByTagName("DayBack")(0).appendChild doc.createTextNode(sDayBack)
   doc.getElementsByTagName("DateIn")(0).appendChild doc.createTextNode(sDateIn)
   doc.getElementsByTagName("BrokenCode")(0).appendChild doc.createTextNode(sBrokenCode)
   doc.getElementsByTagName("Broken")(0).appendChild doc.createTextNode(sBroken)
   doc.getElementsByTagName("TotalCycles")(0).appendChild doc.createTextNode(sTotalCycles)
   doc.Save sFile
  Next

 End With
End Sub

But i returns an error on this line:

doc.getElementsByTagName("Grai")(0).appendChild doc.createTextNode(sGrai)

with the comment:

Error code 91: Object variable or bock variable is not set

Can you please help me out?

Use `&` to concatenate strings e.g. `sFile = .Cells(lRow, 1).Value & ".xml"` — Robin Mackenzie, Oct 01 '17 at 11:44
Thanks Robin, the replacement works. But now i get another error ( see update in the post) . — Erik hoeven, Oct 01 '17 at 11:56
You can't have spaces in tag names e.g. `Day Date Out` and `Retailer country` etc. See this: https://stackoverflow.com/questions/3480887/how-to-include-space-in-xml-tag-element-which-gets-transformed-by-xslt-into-exce. — Robin Mackenzie, Oct 01 '17 at 12:10
Thanks again Robin. As you can see i have no space in the taggs anymore but i get still the same error. — Erik hoeven, Oct 01 '17 at 12:24
Your template has `GRAI` and your code line with the error uses `Grai`. I believe `getElementsByTagName` is case sensitive. — Robin Mackenzie, Oct 01 '17 at 12:26
Robin i change GRAI to Grai in the template but i does not help — Erik hoeven, Oct 01 '17 at 12:30
Does the error still occur on `doc.getElementsByTagName("Grai")(0).appendChild doc.createTextNode(sGrai)` ? — Robin Mackenzie, Oct 01 '17 at 12:41
How have you declared `sGrai`? If you don't use `Option Explicit` and you haven't declared `sGrai` then numerical values in your cell won't be converted to strings. This might be the issue. Try `Dim sGrai As String` and, to be safe, `sGrai = CStr(.Cell(lRow, 1).Value)`. — Ambie, Oct 02 '17 at 00:48
Thans Ambi, thats works. Do you also know how i change the location. I think default it will put all the xml documents in "my documents" — Erik hoeven, Oct 02 '17 at 06:43
Post that as a new question. Just remember to include what you've tried. — Ambie, Oct 02 '17 at 10:32
Ambie thanks for your comment see question ( https://stackoverflow.com/questions/46530592/vba-excel-rows-to-xml-output-on-different-output-folder ) — Erik hoeven, Oct 02 '17 at 17:29

score 0 · Answer 1 · answered Oct 02 '17 at 17:23

Based on the comment of Ambi the corrected and working vba script is:

Sub xmlPerRow()

 sTemplateXML = _
        "<?xml version='1.0'?>" + vbNewLine + "<data>" + vbNewLine + "<Grai>" + vbNewLine + "</Grai>" + vbNewLine + _
        "   <DayDateOut>" + vbNewLine + "   </DayDateOut>" + vbNewLine + "   <Filler>" + vbNewLine + "   </Filler>" + vbNewLine + _
        "   <FillerCountry>" + vbNewLine + "   </FillerCountry>" + vbNewLine + "   <Retailer>" + vbNewLine + "   </Retailer>" + vbNewLine + _
        "   <RetailerCountry>" + vbNewLine + "   </RetailerCountry>" + vbNewLine + "   <Days>" + vbNewLine + "   </Days>" + vbNewLine + _
        "   <DayBack>" + vbNewLine + "   </DayBack>" + vbNewLine + "   <DateIn>" + vbNewLine + "   </DateIn>" + vbNewLine + _
        "   <BrokenCode>" + vbNewLine + "   </BrokenCode>" + vbNewLine + "   <Broken>" + vbNewLine + "   </Broken>" + vbNewLine + _
        "   <TotalCycles>" + vbNewLine + "   </TotalCycles>" + vbNewLine + "</data>" + 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 = "Grai_" & .Cells(lRow, 1).Value & ".xml"
   Dim sGrai As String
   sGrai = CStr(.Cells(lRow, 1).Value)
   sDayDateOut = .Cells(lRow, 2).Value
   sFiller = .Cells(lRow, 3).Value
   sFillerCountry = .Cells(lRow, 4).Value
   sRetailer = .Cells(lRow, 5).Value
   sRetailerCountry = .Cells(lRow, 6).Value
   sDays = .Cells(lRow, 7).Value
   sDayBack = .Cells(lRow, 8).Value
   sDateIn = .Cells(lRow, 9).Value
   sBrokenCode = .Cells(lRow, 10).Value
   sBroken = .Cells(lRow, 11).Value
   sTotalCycles = .Cells(lRow, 12).Value

   doc.LoadXML sTemplateXML
   doc.getElementsByTagName("Grai")(0).appendChild doc.createTextNode(sGrai)
   doc.getElementsByTagName("DayDateOut")(0).appendChild doc.createTextNode(sDayDateOut)
   doc.getElementsByTagName("Filler")(0).appendChild doc.createTextNode(sFiller)
   doc.getElementsByTagName("RetailerCountry")(0).appendChild doc.createTextNode(sRetailerCountry)
   doc.getElementsByTagName("Retailer")(0).appendChild doc.createTextNode(sRetailer)
   doc.getElementsByTagName("Days")(0).appendChild doc.createTextNode(sDays)
   doc.getElementsByTagName("DayBack")(0).appendChild doc.createTextNode(sDayBack)
   doc.getElementsByTagName("DateIn")(0).appendChild doc.createTextNode(sDateIn)
   doc.getElementsByTagName("BrokenCode")(0).appendChild doc.createTextNode(sBrokenCode)
   doc.getElementsByTagName("Broken")(0).appendChild doc.createTextNode(sBroken)
   doc.getElementsByTagName("TotalCycles")(0).appendChild doc.createTextNode(sTotalCycles)
   doc.Save sFile
  Next

 End With
End Sub

Error while converting excelsheet to XML

1 Answers1