ronadair
asked on
Open XML file in Excel 2010.
When I try to open an XML file in Excel 2010 I get an error message than indicates there is an XML Parse Error. Import Failed. The specific reason is that DTD is prohibited & the location is on line 2.
I've seen code that eliminates the 2nd line and then saves the file as a duplicate, which then excel can open (apparently). I don't know where to put the code so I can always access it or how to relate the XML file to the code.
I have a reasonable knowledge of Excel, but not much background in VBA. Please offer detailed assistance, including code.
Thanks,
Ron
I've seen code that eliminates the 2nd line and then saves the file as a duplicate, which then excel can open (apparently). I don't know where to put the code so I can always access it or how to relate the XML file to the code.
I have a reasonable knowledge of Excel, but not much background in VBA. Please offer detailed assistance, including code.
Thanks,
Ron
markscha
Do you mean OpenXML (as in the zipped XML-based Office file format) or Open an XML (import XML into Excel)?
ASKER
Import XML into Excel 2010, then save as CSV format.
Do you have an example of the offending XML?
Please open the XML file in notepad.
Look for something similar to:
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocat
If you remove that part of the node, there is no longer a DTD attached to the XML file.
For example, if you have this header:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<companies xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocat
you can change that to:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<companies>
Then save the XML file (save-as to keep the original!) and load the copy into Excel.
If you're not sure, copy the first couple of nodes and paste them here.
Look for something similar to:
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocat
If you remove that part of the node, there is no longer a DTD attached to the XML file.
For example, if you have this header:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<companies xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocat
you can change that to:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<companies>
Then save the XML file (save-as to keep the original!) and load the copy into Excel.
If you're not sure, copy the first couple of nodes and paste them here.
ASKER
I think the problem is with the second line below. The error message indicates 'DTD" as the problem.
<?xml version="1.0"?>
<!DOCTYPE MstarPort SYSTEM "MstarPort.dtd">
<?xml version="1.0"?>
<!DOCTYPE MstarPort SYSTEM "MstarPort.dtd">
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
I've got a question about the DOCTYPE declaration. If I receive an XML file with something like “<!DOCTYPE xxxx SYSTEM "dailyABSubmission.dtd">” in the 2nd line of the file and I actually HAVE the "dailyABSubmission.dtd" file that the XML references, can I just put the DTD somewhere so that Excel can reference it and not have a problem with the DOCTYPE declaration?