Error -1072896636 "DTD Prohibited" when opening xml file in Excel, what do I do?

Question

I'm trying to open an XML file in Excel 2013, but it says it can't do that because "DTD prohibited". I've searched for the solution, but all I found are some pieces of code that are supposed to solve the problem.

But I don't get it at all! What code? I'm just opening the file, I'm not writing a program or anything. Should I recite that code aloud before opening a file or what? Sorry, but I find it really absurd.

Is there some solution to just open the file without writing a program for it? And if not, what should I do with this code?

The code I'm talking about is the following

Set xmlDoc = CreateObject("Msxml2.DOMDocument.6.0")
xmlDoc.setProperty "ProhibitDTD",False
xmlDoc.load xmltoload.xml

I've discovered that the file may be supposed to be opened in Word, not Excel, but the Word 2013 gives me the same error.

Answer:
It appeared the file was supposed to be opened in Excel after all. I've found on the Internet that Office 2007 and older doesn't prohibit DTD whatever that is. So I successfully bypassed the problem by opening the needed file in Excel 2003.

Answer 1

The workaround is pretty easy. Open the XML file in a text editor and delete the line that is referring to a DTD location.

Excel will then create an XML schema according to your XML file.

Answer 2

All XML documents rely upon a definition of what fields are allowed to exist and what values are allowed to exist within each of those fields. These are defined within each XML file by linking to either a Document Type Definition ("DTD") or an XML Schema file.

My understanding is that Excel 2007 and later do NOT support DTD. Instead, they expect that your XML file will reference an XML Schema.

So, your underlying problem is that your XML file is referencing a DTD file and you need to convert your DTD file to an XML Schema file (you can verify this by opening the document with a program like Notepad++ and looking at the raw text.)

There are a lot of tools out there that help you do this but, frankly, I've been trying to get them all to work for a few hours and still don't have Excel opening my XML (ran across this post googling the same error code.)

If you are just trying to open an XML file one-off you probably don't want to put that much effort into it -- but if I figure out a quick and easy solution I'll come update.

If you just need to open it there is a free program you can download that converts XML to CSV:

http://xmltocsv.codeplex.com/

I tried it and it worked great -- got my file to open in Excel 2013 immediately. Problem solved for now.

Of course, the point of XML is that data can relate to other data with context and that is lost without a proper schema or DTD file. But if you just want the file to open, this should work for now (for those who don't still have Excel 2003 lying around, though I imagine that's just as easy for you.)