CODEWITHSUNDEEP
xmlvbaexcelexport
samj
samj

Reputation: 1

Excel to XML export via VBA

I have been using the template in the answer to this question in order to export data from an Excel spreadsheet into separate XML files for each row. I've modified the template in that post to match the output I need, yet when I run the macro I get a "Runtime Error 91: Object Variable or With block variable not set." Here is the VBA Macro I'm currently using: 

Sub xmlconverter1()

sTemplateXML = "<?xml version='1.0'?>" + vbNewLine + _
"<mods xmlns='http://www.loc.gov/mods/v3' xmlns:mods='http://www.loc.gov/mods/v3' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xlink='http://www.w3.org/1999/xlink'>" + vbNewLine + _
    "   <titleInfo>" + vbNewLine + _
    "       <title></title>" + vbNewLine + _
    "   </titleInfo>" + vbNewLine + _
    "   <typeOfResource>" + vbNewLine + _
    "   </typeOfResource>" + vbNewLine + _
    "   <name type='personal'>" + vbNewLine + _
    "       <namePart></namePart>" + vbNewLine + _
    "   </name>" + vbNewLine + _
    "   <abstract>" + vbNewLine + _
    "   </abstract>" + vbNewLine + _
    "   <identifier>" + vbNewLine + _
    "   </identifier>" + vbNewLine + _
    "   <originInfo>" + vbNewLine + _
    "       <dateIssued></dateIssued>" + vbNewLine + _
    "   </originInfo>" + vbNewLine + _
    "   <physicalDescription>" + vbNewLine + _
    "       <form></form>" + vbNewLine + _
    "       <extent></extent>" + vbNewLine + _
    "   <subject>" + vbNewLine + _
    "       <geographic></geographic>" + vbNewLine + _
    "   </subject>" + vbNewLine + _
    "</mods>" + 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
 sTitle = .Cells(lRow, 2).Value
 sType = .Cells(lRow, 11).Value
 sCreator = .Cells(lRow, 3).Value
 sDescription = .Cells(lRow, 6).Value
 sIdentifier = .Cells(lRow, 1).Value
 sDate = Format(.Cells(lRow, 9).Value, "YYYY-MM-DD")
 sForm = .Cells(lRow, 12).Value
 sExtent = .Cells(lRow, 22).Value
 sCoverage = .Cells(lRow, 7).Value
 doc.LoadXML sTemplateXML
 doc.getElementsByTagName("title")(0).appendChild doc.createTextNode(sTitle)
 doc.getElementsByTagName("typeOfResource")(0).appendChild doc.createTextNode(sType)
 doc.getElementsByTagName("name")(0).appendChild doc.createTextNode(sCreator)
 doc.getElementsByTagName("abstract")(0).appendChild doc.createTextNode(sDescription)
 doc.getElementsByTagName("identifier")(0).appendChild doc.createTextNode(sIdentifier)
 doc.getElementsByTagName("dateIssued")(0).appendChild doc.createTextNode(sDate)
 doc.getElementsByTagName("form")(0).appendChild doc.createTextNode(sForm)
 doc.getElementsByTagName("extent")(0).appendChild doc.createTextNode(sExtent)
 doc.getElementsByTagName("geographic")(0).appendChild doc.createTextNode(sCoverage)
 doc.Save sIdentifier

Next

 End With

End Sub

What can I change that will get rid of this error and still give me the output in the format I need?

Upvotes: 0

Views: 2564

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

doc.validateOnParse = False

is hiding the parse error:

End tag 'mods' does not match the start tag 'physicalDescription'.

You haven't closed the tag <physicalDescription> in your XML template

Upvotes: 2

Related Questions