SOLVED

Undeclared Prefix Error in one sheet - XML

Copper Contributor

I have an excel file template that is consistently giving me a XML error on one sheet every time it is opened. I've unzipped the excel file to get the specific sheet prior to excel trying to recover it and validated against an XML online validator. It keeps giving me "error on line 2 at column 11233: Namespace prefix xdr on col is not defined".

 

See below for the specific XML that is referencing xdr. Not sure where xdr should be defined as a namespace, or why excel is leaving it out in the XML definitions. This is a legacy corporate file template so has potentially been through several iterations of Excel.

 

<controls>
    <control shapeId="2049" r:id="rId3" name="Drop Down 1">
        <controlPr defaultSize="0" print="0" uiObject="1" autoLine="0" autoPict="0">
            <anchor sizeWithCells="1">
                <from>
                    <xdr:col>2</xdr:col>
                    <xdr:colOff>0</xdr:colOff>
                    <xdr:row>15</xdr:row>
                    <xdr:rowOff>0</xdr:rowOff>
                </from>
                <to>
                    <xdr:col>6</xdr:col>
                    <xdr:colOff>85725</xdr:colOff>
                    <xdr:row>16</xdr:row>
                    <xdr:rowOff>0</xdr:rowOff>
                </to>
            </anchor>
        </controlPr>
    </control>
</controls>

 

2 Replies
best response confirmed by Prometheus22 (Copper Contributor)
Solution
xdr is one of the OfficeOpen XML namespaces. As a namespace, it has to be defined before using it. So in either parent element, just do the following :

<controls xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing">

Clarifying for any people that find this post in the future and are not XML oriented. Correct code snippet is below. Would add in @Stephane_Zwel's 

xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"

 within the original XML as 

<controls 
  xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing">
    <control shapeId="2049" r:id="rId3" name="Drop Down 1">
        <controlPr defaultSize="0" print="0" uiObject="1" autoLine="0" autoPict="0">
            <anchor sizeWithCells="1">
                <from>
                    <xdr:col>2</xdr:col>
                    <xdr:colOff>0</xdr:colOff>
                    <xdr:row>15</xdr:row>
                    <xdr:rowOff>0</xdr:rowOff>
                </from>
                <to>
                    <xdr:col>6</xdr:col>
                    <xdr:colOff>85725</xdr:colOff>
                    <xdr:row>16</xdr:row>
                    <xdr:rowOff>0</xdr:rowOff>
                </to>
            </anchor>
        </controlPr>
    </control>
</controls>

 

1 best response

Accepted Solutions
best response confirmed by Prometheus22 (Copper Contributor)
Solution
xdr is one of the OfficeOpen XML namespaces. As a namespace, it has to be defined before using it. So in either parent element, just do the following :

<controls xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing">

View solution in original post