Using VB in Excel to convert to each row to individual xml files.

[S. Hand]

Hi All,

I have this bit of code "borrowed" from the Internet.  I am attempting to use it to create xml files for each row of metadata for collections (in this case our artifacts.)

Anyone here proficient in VB coding?  I keep getting an error and cannot find a solution on the Internet that resolves it.  By all accounts there shouldn't be an issue at all.

Here is the code:  When I run this macro in Excel it actually works but I get an error and option to debug.  If I click debug it highlights doc.save but I don't know what to do from there.

TIA

Sub testXLStoXML()

 sTemplateXML = _

        "<?xml version='1.0' encoding='UTF-8'?>" + vbNewLine + _

        "<oai_dc:dc xsi:schemaLocation='http://www.openarchives.org/OAI/2.0/oai_dc.xsd' xmlns:dc='http://purl.org/dc/elements/1.1/' xmlns:oai_dc='http://www.openarchives.org/OAI/2.0/oai_dc/' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>" + vbNewLine + _

        "<!-- dublin core -->" + vbNewLine + _

        "   <title></title>" + vbNewLine + _

        "   <creator></creator>" + vbNewLine + _

        "   <subject></subject>" + vbNewLine + _

        "   <description></description>" + vbNewLine + _

        "   <publisher></publisher>" + vbNewLine + _

        "   <contributor></contributor>" + vbNewLine + _

        "   <date></date>" + vbNewLine + _

        "   <type></type>" + vbNewLine + _

        "   <format></format>" + vbNewLine + _

        "   <identifier></identifier>" + vbNewLine + _

        "   <source></source>" + vbNewLine + _

        "   <language></language>" + vbNewLine + _

        "   <relation></relation>" + vbNewLine + _

        "   <coverage></coverage>" + vbNewLine + _

        "   <rights></rights>" + vbNewLine + _

        "</oai_dc:dc>" + 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

   stitle = .Cells(lRow, 2).Value

   screator = Format(.Cells(lRow, 3).Value)

   ssubject = Format(.Cells(lRow, 4).Value)

   sdescription = .Cells(lRow, 5).Value

   spublisher = .Cells(lRow, 6).Value

   scontributor = .Cells(lRow, 7).Value

   sdate = .Cells(lRow, 8).Value

   stype = .Cells(lRow, 9).Value

   sformat = .Cells(lRow, 10).Value

   sidentifier = .Cells(lRow, 11).Value

   ssource = .Cells(lRow, 12).Value

   slanguage = .Cells(lRow, 13).Value

   srelation = .Cells(lRow, 14).Value

   scoverage = .Cells(lRow, 15).Value

   srights = .Cells(lRow, 16).Value

   doc.LoadXML sTemplateXML

   doc.getElementsByTagName("title")(0).appendChild doc.createTextNode(stitle)

   doc.getElementsByTagName("creator")(0).appendChild doc.createTextNode(screator)

   doc.getElementsByTagName("subject")(0).appendChild doc.createTextNode(ssubject)

   doc.getElementsByTagName("description")(0).appendChild doc.createTextNode(sdescription)

   doc.getElementsByTagName("publisher")(0).appendChild doc.createTextNode(spublisher)

   doc.getElementsByTagName("contributor")(0).appendChild doc.createTextNode(scontributor)

   doc.getElementsByTagName("date")(0).appendChild doc.createTextNode(sdate)

   doc.getElementsByTagName("type")(0).appendChild doc.createTextNode(stype)

   doc.getElementsByTagName("format")(0).appendChild doc.createTextNode(sformat)

   doc.getElementsByTagName("identifier")(0).appendChild doc.createTextNode(sidentifier)

   doc.getElementsByTagName("source")(0).appendChild doc.createTextNode(ssource)

   doc.getElementsByTagName("language")(0).appendChild doc.createTextNode(slanguage)

   doc.getElementsByTagName("relation")(0).appendChild doc.createTextNode(srelation)

   doc.getElementsByTagName("coverage")(0).appendChild doc.createTextNode(scoverage)

   doc.getElementsByTagName("rights")(0).appendChild doc.createTextNode(srights)

   doc.Save sFile

  Next

 End With

End Sub