CODEWITHSUNDEEP
excelvba
Sallie Francis
Sallie Francis

Reputation: 1

Rows to individual xml files including content value

I have a sheet that has 7 headers. I want to convert each row and column values to individual xml files.

I am using this thread to compile.

I get

Runtime Error 91 Object Variable or With block variable not set.

Sub Export()

sTemplateXML = _
        "<data>" + vbNewLine + _
        "   <Name/>" + vbNewLine + _
        "   <Extension/>" + vbNewLine + _
        "   <AXCustNum/>" + vbNewLine + _
        "   <CustomerName/>" + vbNewLine + _
        "   <TitlewithExtension/>" + vbNewLine + _
        "   <Title/>" + vbNewLine + _
        "   <Folder/>" + vbNewLine + _
        "</data>" + vbNewLine

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

With Sheets("SAL Checked File Names 1.9")
  lLastRow = .UsedRange.Rows.Count

 For lRow = 2 To lLastRow
   sname = .Cells(lRow, 1).Value
   sExtension = .Cells(lRow, 2).Value
   saxcustnum = .Cells(lRow, 3).Value
   scustomername = .Cells(lRow, 4).Value
   stitlewithextension = .Cells(lRow, 5).Value
   sTitle = .Cells(lRow, 6).Value
   sFolder = .Cells(lRow, 7).Value
   
   
   doc.LoadXML sTemplateXML
   doc.getElementsByTagName("Name")(0).appendChild doc.createTextNode(sname)
   doc.getElementsByTagName("Extension")(0).appendChild doc.createTextNode(sExtension)
   doc.getElementsByTagName("AX.CustNum")(0).appendChild doc.createTextNode(saxcustnum)
   doc.getElementsByTagName("CustomerName")(0).appendChild doc.createTextNode(scustomername)
   doc.getElementsByTagName("TitlewithExtension")(0).appendChild doc.createTextNode(stitlewithextension)
   doc.getElementsByTagName("Title")(0).appendChild doc.createTextNode(sTitle)
   doc.getElementsByTagName("Folder")(0).appendChild doc.createTextNode(sFolder)

   doc.Save sFolder
   Next
   
   End With
 
End Sub

Upvotes: 0

Views: 58

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

You have

 "   <AXCustNum/>" + vbNewLine + _

in the template XML but

doc.getElementsByTagName("AX.CustNum")(0).appendChild doc.createTextNode(saxcustnum)

later, so getElementsByTagName fails to make a match.

Upvotes: 1

Related Questions