Is there a way to get Excel to save the XML attributes in the root element?

Question

I experimented using MS Excel 2007 to edit tabular data stored in an XML file. It's a good job to import and even validate XML data against a schema (xsd file), but when exported, it discards the xmlns, xlmns: xsi and xsi: schemaLocation attributes from the root element. It also changes the default namespace to an explicit namespace.

Here is a before / after comparison:

Before (XML file before import into Excel)

<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <database xmlns="experimentManager" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="experimentManager Database.xsd"> <conditionTokens> ... </conditionTokens> <participants> ... </participants> </database> 

After (XML file after export from Excel)

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ns1:database xmlns:ns1="experimentManager"> <ns1:conditionTokens> ... </ns1:conditionTokens> <ns1:participants> ... </ns1:participants> </ns1:database> 

Is there a way to prevent Excel from leaking these attributes and messing around with namespaces? I read MS help on XML mapping and import / export, but there seems to be no parameters in the GUI for what I want to do. If I need to write a custom macro, this is an option, but I would rather not do it if there is a better / easier way.

Second question: is there a better tool to facilitate editing some parts of XML files using a user interface like Excel?

Answer 1

Ok, ok, I removed the bullet and wrote a nice VBA macro. I decided that I would share with you all if someone else runs into the same problem.

This macro basically calls the built-in XML Export () method of Excel, and then performs a series of text replacements in the resulting file. Text replacements are entirely up to you. Just place them on a sheet, as in the link below ...

Example of setting replacement rules: Click me for a screen

In this example, I replaced the tab with a space ": ns1" with an empty, "ns1:" with an empty and truncated root element with the original root element.

You can format your replacement rules in any way convenient for you if you follow these instructions:

• Select all the "find what" cells and give them the name * "FindWhat" (do not include the selection line in your selection, spaces will be ignored).
• Select all the cells "replace to" and give them the name * "ReplaceWith" (there should be a one-to-one mapping between the cells "find what" and "replace with", use spaces to remove unwanted text).
• Enter the name of the XML map somewhere in your book and name this cell "XmlMap".
• Run the macro. (You will be prompted to specify the file you want to export.)

* If you are not familiar with naming ranges in Excel 2007, go to the Formulas tab and select Name Manager.

OK, I won’t keep you on your toes (LOL) ... here is the code for the macro. Just put it in the module in the VBA editor. I do not offer any guarantees with this free code (you can easily break it if you name the ranges incorrectly), but the examples I tried worked for me.

 Option Explicit Sub ExportXml() Dim exportResult As XlXmlExportResult Dim exportPath As String Dim xmlMap As String Dim fileContents As String exportPath = RequestExportPath() If exportPath = "" Or exportPath = "False" Then Exit Sub xmlMap = range("XmlMap") exportResult = ActiveWorkbook.XmlMaps(xmlMap).Export(exportPath, True) If exportResult = xlXmlExportValidationFailed Then Beep Exit Sub End If fileContents = ReadInTextFile(exportPath) fileContents = ApplyReplaceRules(fileContents) WriteTextToFile exportPath, fileContents End Sub Function ApplyReplaceRules(fileContents As String) As String Dim replaceWorksheet As Worksheet Dim findWhatRange As range Dim replaceWithRange As range Dim findWhat As String Dim replaceWith As String Dim cell As Integer Set findWhatRange = range("FindWhat") Set replaceWithRange = range("ReplaceWith") For cell = 1 To findWhatRange.Cells.Count findWhat = findWhatRange.Cells(cell) If findWhat <> "" Then replaceWith = replaceWithRange.Cells(cell) fileContents = Replace(fileContents, findWhat, replaceWith) End If Next cell ApplyReplaceRules = fileContents End Function Function RequestExportPath() As String Dim messageBoxResult As VbMsgBoxResult Dim exportPath As String Dim message As String message = "The file already exists. Do you want to replace it?" Do While True exportPath = Application.GetSaveAsFilename("", "XML Files (*.xml),*.xml") If exportPath = "False" Then Exit Do If Not FileExists(exportPath) Then Exit Do messageBoxResult = MsgBox(message, vbYesNo, "File Exists") If messageBoxResult = vbYes Then Exit Do Loop RequestExportPath = exportPath End Function Function FileExists(path As String) As Boolean Dim fileSystemObject Set fileSystemObject = CreateObject("Scripting.FileSystemObject") FileExists = fileSystemObject.FileExists(path) End Function Function ReadInTextFile(path As String) As String Dim fileSystemObject Dim textStream Dim fileContents As String Dim line As String Set fileSystemObject = CreateObject("Scripting.FileSystemObject") Set textStream = fileSystemObject.OpenTextFile(path) fileContents = textStream.ReadAll textStream.Close ReadInTextFile = fileContents End Function Sub WriteTextToFile(path As String, fileContents As String) Dim fileSystemObject Dim textStream Set fileSystemObject = CreateObject("Scripting.FileSystemObject") Set textStream = fileSystemObject.CreateTextFile(path, True) textStream.Write fileContents textStream.Close End Sub 

Answer 2

This is actually a lot easier.

• Change the suffix .xlsx to .zip - the xlsx format is actually zipped with xml files!
• Open the zip file in Windows Explorer
• Go to the xl subdirectory
• Copy the xmlMaps.xml file to a folder outside the .zip folder
• Edit the file to replace the nsX: entries with your preferred namespace and save the changes.
• Copy the file and overwrite the version in the .zip folder
• Rename the folder back to .xslx

Your xml map will now show your preferred namespaces.

Answer 3

I tried to accept the accepted answer, and, like the person presented below, I got a global Range error, since Excel is very difficult to determine the range of the XML file. However, I created an alternative method for this, which I would not want to do, but, unfortunately, the banking system cannot accept the XML file with ns1: in it.

If you just want something to be deleted, use the following command to save the file as a text file, then open it again in Excel, delete ns1: and save it as .prn. You only need to change prn to xml.

 Workbooks.OpenText Filename:= _ Store & "\" & "\" & sFilename & ".txt", _ Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _ TrailingMinusNumbers:=True Cells.Replace What:="ns1:", Replacement:="", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False ActiveWorkbook.SaveAs Filename:= _ Store & "\" & "\" & sFilename & ".prn", _ FileFormat:=xlTextPrinter, CreateBackup:=False ActiveWorkbook.Close savechanges:=False