Importing XML Crashes Excel

Question

I have had this recurring problem with all of my spreadsheets for months. The spreadsheet will be working fine and importing the xml files fetched from our website, then all of a sudden it will start crashing everytime on import. The only thing that fixes it is to take all of the contents (sheets, code, references) and drop them into a fresh workbook.

This is the line it crashes on.

ActiveWorkbook.XmlImport URL:=l_strXMLFileName, _
ImportMap:=Nothing, Destination:=Sheets("Imported Data").Range("$A$1")

Has anyone had this problem? If so, is there a better way to keep it from crashing??

Answer 1

Is there a chance the input XML has some illegal / unescaped characters?

Answer 2

I posted this question after I found the answer just because this was such a disaster for me, and had plagued me for months. Hopefully this post will keep someone else from pulling their hair out like I did for so long.

The fix is pretty simple. What happens is every time excel imports an xml file, it stores an xmlmap in that spreadsheet. So if you use the same spreadsheet and keep saving it, these build up over time. The best solution I have found is to just delete these xml maps, or reuse the same one (the latter is not a good option many times).

Here is some code to delete all of them (I just run this before the code I included in my question):

Dim XmlMap as XmlMap
    For Each XmlMap In ActiveWorkbook.XmlMaps
        XmlMap.Delete
    Next