Building XML data files - avoiding the "cannot save or export XML data" error

Submeg · Feb 11th 2017

Hello,

I am trying to export an xml file, however, each time I am ready, I receive the following error pop up:

"Cannot save or export XML data. The XML maps in this workbook are not exportable."

The reason I am trying to export the XML file is as follows:

1. I have been using iTunes, on a Mac. I want to switch to jRiver Media Center on a Windows based PC.
2. I exported my iTunes library as an .xml file.
3. I imported this .xml file into jRiver on my Mac. It retained all of my metadata on the files.
4. I exported my jRiver library as a .xml file.
5. I imported my jRiver library .xml file into jRiver on my Windows machine.

Unfortunately, due to the fact that a Mac based OS has the slashes in the file path in the opposite direction to windows, all of the file structures of the songs in the library are in the wrong direction (i.e. / instead of \) and jRiver couldn't link to any of the songs.

I then did the following:

6. Imported the .xml file into excel.

But because it didn't know the structure of the xml file, it created its own. I did a bit of searching, and came across this site, which explains how to create that background file (which I was able to create successfully).

I updated the direction of the slashes with this code -

Code

Sub SlashSwap()


Dim LastRow As Long


Dim FilePath As String
Dim FilePathEdit As String


LastRow = 0


FilePath = ""
FilePathEdit = ""


'First, find ensure your library is filtered to display only the filepaths.
'Second, find the length of this list.


Range("A1").Select
Selection.End(xlDown).Select


LastRow = ActiveCell.Offset(1, 0).Row


'Next, begin the loop.


Range("D2").Select


Do While ActiveCell.Row < LastRow


    'Ensure that the row is not filtered.
    If Not ActiveCell.Rows.Hidden Then


          FilePath = ActiveCell.Value
          'Check to see if the string has any "/" slashes.


          Do While InStr(1, ActiveCell.Value, "/") <> 0
                 'Replace all the "/" with "\".
                 FilePathEdit = Replace(FilePath, "/", "\", 1)
                 ActiveCell.Value = FilePathEdit


         Loop
    
    End If


    FilePath = ""
    FilePathEdit = ""
    ActiveCell.Offset(1, 0).Select


Loop
End Sub

Display More

My final step was then to export this updated data as an .xml data file. When I try to do that, I receive the error:

"Cannot save or export XML data. The XML maps in this workbook are not exportable."

I did some digging further, and apparently, Excel has trouble dealing with complex .xml files. I was able to find this page, which may suggest that the structure of the .xml file may be too complex?

Here is an sample of the library.xml file - a single song (there are around 35,000+ of these entries in this xml library file):

XML

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<MPL Version="2.0" Title="Library" PathSeparator="\">
<Item>
<Field Name="Filename">E:\iPod Library\Bonfanti\DOWNLOAD\11 Ambiente Elegante.m4a</Field>
<Field Name="Name">Ambiente Elegante</Field>
<Field Name="Artist">Bonfanti</Field>
<Field Name="Album">DOWNLOAD</Field>
<Field Name="Genre">break</Field>
<Field Name="Comment">8A - 108</Field>
<Field Name="Bitrate">256</Field>
<Field Name="Rating">3</Field>
<Field Name="Duration">157</Field>
<Field Name="Track #">11</Field>
<Field Name="Media Type">Audio</Field>
<Field Name="Date Imported">1486276195</Field>
<Field Name="Date First Rated">1486275693</Field>
<Field Name="File Type">m4a</Field>
<Field Name="File Size">5121597</Field>
<Field Name="Date Created">1309002556</Field>
<Field Name="Date Modified">1345978598</Field>
<Field Name="Compression">MP4 AAC</Field>
<Field Name="BPM">108</Field>
<Field Name="Sample Rate">44100</Field>
<Field Name="Channels">2</Field>
<Field Name="Bit Depth">16</Field>
<Field Name="Initial Key">8A</Field>
</Item>
<Item>
.....
</Item>
</MPL>

Display More

On another site, it suggested I had to build my own .xml file using vba, rather than utilising the created schema (a.k.a. xml template) file, I could code the .xml file directly, without the use of the schema. Is this even possible?

Any information about successfully outputting an .xml data file would be appreciated. Thank you.

Submeg

Submeg · Feb 12th 2017

Re: Building XML data files - avoiding the "cannot save or export XML data" error

I was able to find a work around to both the original problem and the xml export problem.

To avoid the "Cannot save or export XML data. The XML maps in this workbook are not exportable." error, I manually rebuilt the .xml file myself. An example as below. Once you create all the headers, all that you have to do is paste in the body text.

Code

Sub XMLCreate()


Dim myFile As String
Dim cellValue As Variant
Dim RowCount As Long
Dim ColumnCount As Long
Dim i As Integer
Dim j As Integer


Set fs = CreateObject("Scripting.FileSystemObject")
Set XMLFile = fs.CreateTextFile("c:\J River" & "\XMLBuild.xml", True)
'myFile = "c:\J River" & "\XMLBuild.xml"


Range("A1").Select
Selection.End(xlToRight).Select
ColumnCount = ActiveCell.Column


Range("A1").Select
Selection.End(xlDown).Select
RowCount = ActiveCell.Row


'Set up the header row
XMLFile.WriteLine ("<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF-8" & Chr(34) & " standalone=" & Chr(34) & "yes" & Chr(34) & " ?>")
XMLFile.WriteLine ("<MPL Version=" & Chr(34) & "2.0"...and so on...)




'For RowLoop = 1 To RowCount
'    For ColumnLoop = 1 To ColumnCount
'
'        cellValue = Cells(RowLoop, ColumnLoop).Value
'
'            XMLFile.WriteLine (cellValue)
'
'    Next ColumnLoop
'Next RowLoop




End Sub

Display More

**pike** · Feb 12th 2017

Re: Building XML data files - avoiding the "cannot save or export XML data" error

thanks for taking the time to post the answer. It will help others with a similar problem

Building XML data files - avoiding the "cannot save or export XML data" error

Participate now!

Share

Users Viewing This Thread