Export excel rows to individual XML files via VBA

Question

I'm following this answer (Convert rows of spreadsheet to separate XML files), however I'm getting an error telling me an object is not defined. I apologize as I'm new to VBA.

My table follows the order shown in the code, e.g.

Column 1 {e.g. A} = file name string
Column 677 {e.g. ZA} = string for first line of XML file
Column 683 {e.g. ZG} = Title of video file {this and all following columns already have their appropriate tags (e.g. <title{>} - (<)/title>) built into each row's cell values themselves}

My code that's producing the error is:

    Sub testXLStoXML()

sTemplateXML = _
        "<xml_version>" + vbNewLine + _
        "<xml_movie>" + vbNewLine + _
        "<plot>" + vbNewLine + _
        "<xml_outline>" + vbNewLine + _
        "<xml_lockdata>" + vbNewLine + _
        "<dateadded>" + vbNewLine + _
        "<title>" + vbNewLine + _
        "<rating>" + vbNewLine + _
        "<year>" + vbNewLine + _
        "<sorttile>" + vbNewLine + _
        "<mpaa>" + vbNewLine + _
        "<premiered>" + vbNewLine + _
        "<releasedate>" + vbNewLine + _
        "<runtime>" + vbNewLine + _
        "<studio>" + vbNewLine + _
        "<1>" + vbNewLine + _
        "<2>" + vbNewLine + _
        "<3>" + vbNewLine + _
        "<folder>" + 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, 2).Value
   sXml_version = .Cells(lRow, 677).Value
   sXml_movie = .Cells(lRow, 678).Value
   sPlot = .Cells(lRow, 679).Value
   sXml_outline = .Cells(lRow, 680).Value
   sXml_lockdata = .Cells(lRow, 681).Value
   sDateadded = .Cells(lRow, 682).Value
   sTitle = .Cells(lRow, 683).Value
   sRating = .Cells(lRow, 684).Value
   sYear = .Cells(lRow, 685).Value
   sSorttile = .Cells(lRow, 686).Value
   sMpaa = .Cells(lRow, 687).Value
   sPremiered = .Cells(lRow, 688).Value
   sReleasedate = .Cells(lRow, 689).Value
   sRuntime = .Cells(lRow, 690).Value
   sStudio = .Cells(lRow, 691).Value
   s1 = .Cells(lRow, 692).Value
   s2 = .Cells(lRow, 693).Value
   s3 = .Cells(lRow, 694).Value
   sFolder = .Cells(lRow, 800).Value
   doc.LoadXML sTemplateXML
   doc.getElementsByTagName("xml_version")(0).appendChild doc.createTextNode(sXml_version)
   doc.getElementsByTagName("xml_movie")(0).appendChild doc.createTextNode(sXml_movie)
   doc.getElementsByTagName("plot")(0).appendChild doc.createTextNode(sPlot)
   doc.getElementsByTagName("xml_outline")(0).appendChild doc.createTextNode(sXml_outline)
   doc.getElementsByTagName("xml_lockdata")(0).appendChild doc.createTextNode(sXml_lockdata)
   doc.getElementsByTagName("dateadded")(0).appendChild doc.createTextNode(sDateadded)
   doc.getElementsByTagName("title")(0).appendChild doc.createTextNode(sTitle)
   doc.getElementsByTagName("rating")(0).appendChild doc.createTextNode(sRating)
   doc.getElementsByTagName("year")(0).appendChild doc.createTextNode(sYear)
   doc.getElementsByTagName("sorttile")(0).appendChild doc.createTextNode(sSorttile)
   doc.getElementsByTagName("mpaa")(0).appendChild doc.createTextNode(sMpaa)
   doc.getElementsByTagName("premiered")(0).appendChild doc.createTextNode(sPremiered)
   doc.getElementsByTagName("releasedate")(0).appendChild doc.createTextNode(sReleasedate)
   doc.getElementsByTagName("runtime")(0).appendChild doc.createTextNode(sRuntime)
   doc.getElementsByTagName("studio")(0).appendChild doc.createTextNode(sStudio)
   doc.getElementsByTagName("1")(0).appendChild doc.createTextNode(s1)
   doc.getElementsByTagName("2")(0).appendChild doc.createTextNode(s2)
   doc.getElementsByTagName("3")(0).appendChild doc.createTextNode(s3)
   doc.getElementsByTagName("folder")(0).appendChild doc.createTextNode(sFolder)
   doc.Save sFile
  Next

 End With

End Sub

The error being highlighted is " doc.getElementsByTagName("xml_version")(0).appendChild doc.createTextNode(sXml_version)"

Although this may just be the first sequentially.

What I want to do - this is for every row that's not null

create XML/text file
the contents of each file is the row's values - each column is a new line {the range of columns, I imagine is outlined by the code as above}
filename = 2nd column value
folder = the folder in the directory of this excel file, with the same name as the value in the 800th column
file extension = ".NFO"
automatically / without asking me, replace any existing file of the same name

The last 3 are italizied because I haven't figured out to do this yet as it's not in the example. I don't even know where to put the folder value in the code for example, whether it can just be listed alongside the other columns as i've done. I also don't know if I need the "/data" in the template.

To clarifiy, this is to automatically produce NFO files for a media system to read as metadata. I have a lot of videos files that are family orientated so don't have a metadata website to scrape data from.

Edit:

Updated code - this code works!

Sub Export()

sTemplateXML = _
        "<data>" + vbNewLine + _
        "   <_version/>" + vbNewLine + _
        "   <_movie/>" + vbNewLine + _
        "   <plot/>" + vbNewLine + _
        "   <_outline/>" + vbNewLine + _
        "   <_lockdata/>" + vbNewLine + _
        "   <dateadded/>" + vbNewLine + _
        "   <title/>" + vbNewLine + _
        "   <rating/>" + vbNewLine + _
        "   <year/>" + vbNewLine + _
        "   <sorttile/>" + vbNewLine + _
        "   <mpaa/>" + vbNewLine + _
        "   <premiered/>" + vbNewLine + _
        "   <releasedate/>" + vbNewLine + _
        "   <runtime/>" + vbNewLine + _
        "   <studio/>" + vbNewLine + _
        "   <_1/>" + vbNewLine + _
        "   <_2/>" + vbNewLine + _
        "   <_3/>" + vbNewLine + _
        "</data>" + vbNewLine


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

With Sheets("Sheet3")
  lLastRow = .UsedRange.Rows.Count

 For lRow = 3 To lLastRow
   sFile = .Cells(lRow, 2).Value
   s_version = .Cells(lRow, 677).Value
   s_movie = .Cells(lRow, 678).Value
   sPlot = .Cells(lRow, 679).Value
   s_outline = .Cells(lRow, 680).Value
   s_lockdata = .Cells(lRow, 681).Value
   sDateadded = .Cells(lRow, 682).Value
   sTitle = .Cells(lRow, 683).Value
   sRating = .Cells(lRow, 684).Value
   sYear = .Cells(lRow, 685).Value
   sSorttile = .Cells(lRow, 686).Value
   sMpaa = .Cells(lRow, 687).Value
   sPremiered = .Cells(lRow, 688).Value
   sReleasedate = .Cells(lRow, 689).Value
   sRuntime = .Cells(lRow, 690).Value
   sStudio = .Cells(lRow, 691).Value
   sFolder = .Cells(lRow, 3).Value
   s_1 = .Cells(lRow, 692).Value
   s_2 = .Cells(lRow, 693).Value
   s_3 = .Cells(lRow, 694).Value
   
   doc.LoadXML sTemplateXML
   doc.getElementsByTagName("_version")(0).appendChild doc.createTextNode(s_version)
   doc.getElementsByTagName("_movie")(0).appendChild doc.createTextNode(s_movie)
   doc.getElementsByTagName("plot")(0).appendChild doc.createTextNode(sPlot)
   doc.getElementsByTagName("_outline")(0).appendChild doc.createTextNode(s_outline)
   doc.getElementsByTagName("_lockdata")(0).appendChild doc.createTextNode(s_lockdata)
   doc.getElementsByTagName("dateadded")(0).appendChild doc.createTextNode(sDateadded)
   doc.getElementsByTagName("title")(0).appendChild doc.createTextNode(sTitle)
   doc.getElementsByTagName("rating")(0).appendChild doc.createTextNode(sRating)
   doc.getElementsByTagName("year")(0).appendChild doc.createTextNode(sYear)
   doc.getElementsByTagName("sorttile")(0).appendChild doc.createTextNode(sSorttile)
   doc.getElementsByTagName("mpaa")(0).appendChild doc.createTextNode(sMpaa)
   doc.getElementsByTagName("premiered")(0).appendChild doc.createTextNode(sPremiered)
   doc.getElementsByTagName("releasedate")(0).appendChild doc.createTextNode(sReleasedate)
   doc.getElementsByTagName("runtime")(0).appendChild doc.createTextNode(sRuntime)
   doc.getElementsByTagName("studio")(0).appendChild doc.createTextNode(sStudio)
   doc.getElementsByTagName("_1")(0).appendChild doc.createTextNode(s_1)
   doc.getElementsByTagName("_2")(0).appendChild doc.createTextNode(s_2)
   doc.getElementsByTagName("_3")(0).appendChild doc.createTextNode(s_3)
   doc.Save sFolder & sFile & ".NFO"
   Next
   
   End With
 
End Sub

I'm unsure about the last line but I receive and error with just doc.save, I've tried every combination (e.g. "doc.save sFile", "doc.save sFile & "NFO" etc.). I've also tried "doc.Save ThisWorkbook.Path & "/" & sFolder & "/" & sFile & ".NFO".

Your `sTemplateXML` is not valid `XML`. It lacks the root tag `` and all end tags for each other elements. That's why `doc` does not contain valid `XML`. That's why `doc.getElementsByTagName("xml_version")` cannot return something. — Axel Richter, May 22 '21 at 15:55
Thank you, I've now updated my question with re-worked code to fix this. I no longer am getting errors however it is not producing a file and i'm unsure why. — Game Analysis, May 22 '21 at 22:37

Parfait · Accepted Answer · 2021-05-22T18:39:31.513

Technically, you should have received an error on LoadXML (unless it returns an empty doc) since your template XML string is not well-formed for various reasons:

No proper root. You close </data> but never open it with <data> at beginning.
All nodes do not close. Try adding self-closing tags where you can append text nodes later.
Per XML specs rules, node names cannot start with a number and cannot start with xml keyword.

[Definition: A Name is an Nmtoken with a restricted set of initial characters.] Disallowed initial characters for Names include digits, diacritics, the full stop and the hyphen.

Names beginning with the string "xml", or with any string which would match (('X'|'x') ('M'|'m') ('L'|'l')), are reserved for standardization in this or future versions of this specification.

Consider following adjustment using underscore starts for forbidden node names:

sTemplateXML = _
        "<data>" + vbNewLine + _
        "   <_xml_version/>" + vbNewLine + _
        "   <_xml_movie/>" + vbNewLine + _
        "   <plot/>" + vbNewLine + _
        "   <xml_outline/>" + vbNewLine + _
        "   <xml_lockdata/>" + vbNewLine + _
        "   <dateadded/>" + vbNewLine + _
        "   <title/>" + vbNewLine + _
        "   <rating/>" + vbNewLine + _
        "   <year/>" + vbNewLine + _
        "   <sorttile/>" + vbNewLine + _
        "   <mpaa/>" + vbNewLine + _
        "   <premiered/>" + vbNewLine + _
        "   <releasedate/>" + vbNewLine + _
        "   <runtime/>" + vbNewLine + _
        "   <studio/>" + vbNewLine + _
        "   <_1/>" + vbNewLine + _
        "   <_2/>" + vbNewLine + _
        "   <_3/>" + vbNewLine + _
        "   <folder/>" + vbNewLine + _
        "</data>" + vbNewLine

For file name handling, simply concatenate needed parts in doc.Save call which will replace file without warning.

doc.Save SFolder & "\" & sFile & ".NFO"

Thank you for the detailed reply, I've made the changes suggested and adjusted the code below to reflect the new names (i've updated my question to show the new code). I've also made sure the worksheet is the first in the workbook due to the "worksheet(1)" line. I'm now no longer getting errors, however the file is not being created when the macro is run; it doesn't appear anything is happening. Do you have any ideas? — Game Analysis, May 22 '21 at 22:32
I assume you are on Windows? If Mac, use forward slash for path separator. Is `sFolder` an absolute path? If relative, add the fuller path before with path separator. If needing relative to workbook, consider `ThisWorkbook.Path`. Also, see my recent answer on a different [Excel XML VBA question](https://stackoverflow.com/a/67594359/1422451). Namely, avoid use of `ActiveWorkbook`. — Parfait, May 23 '21 at 00:57
It's working, thank you! But how would I integrate ThisWorkbook.Path? would it follow doc.Save? - e.g. doc.Save ThisWorkbook.Path & sFolder & sFile & ".NFO" — Game Analysis, May 23 '21 at 10:39
Yes but don't forget about path separator between concatenated `&` parts. — Parfait, May 23 '21 at 12:13
Got it, thanks for all the help, I'm posting a new question regarding the use of special characters on the export as the above has been resolved. — Game Analysis, May 23 '21 at 20:59

Export excel rows to individual XML files via VBA

1 Answers1

Linked