0

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
Community
  • 1
  • 1
Sallie Francis
  • 1
  • 1
  • "AXCustNum" vs "AX.CustNum" I'm guessing that's the issue but you don't tell us the line where the error happens. Also really helps to declare all your variables. – Tim Williams Sep 01 '22 at 23:23

1 Answers1

1

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.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thanks @Tim, that did the trick. Now I just get a permissions 70 error so I am assuming that is something to do with the file location I have in the excel file. I am investigating. – Sallie Francis Sep 01 '22 at 23:38