none
Excel bug with XML connection path RRS feed

  • Question

  • [Comming from= https://social.technet.microsoft.com/Forums/office/en-US/3ad35e7e-ed80-4526-a46a-79830022a612/excel-bug-with-xml-connection-path?forum=excel ]

    Hi,

    I'm importing data from XML file to Excel, and in a first time all seems to be working correctly.

    But if you save the file and re-open it, the source stops working.

    I'm developing a plugin using Excel interop, so I've explored the clases and I noticed that the first time that you import an XML file, XmlMap.Databinding.SourceUrl has the full path, but after open the Excel file, the XmlMap.Databinding.SourceUrl only stores 230 characters from the real source path. And exploring the file as a zip, the connections.xml file is storing correctly the full path.
    So I don't know why but when you open an Excel file excel is trimming the SourceUrl to 230 chars and I think that is an incorrect behavior.

    Note: I've tested this on Excel 2007 and Excel 2013

    Note2: Also I've seen that Excel validates that the path is less than 256 chars, It's curious that when opens an Excel file only reads 230

    regards,

    Sergio.

    Tuesday, March 31, 2015 7:48 AM

Answers

  • Ah, I do see the problem. In the connections.xml, the url is correct. But Excel must not be reading the entire string from that attribute (i.e. truncating). I've checked [MS-OI29500] and it looks like for webPr's url attribute (or any of webPr's attr's) we don't have any length specifications. I will submit this as a request to update [MS-OI29500]. Excel is just following the limitation in Windows.

    Tom

    Monday, April 6, 2015 8:48 PM
    Moderator
  • And sorry, I meant to add that the fact that it reports an error instead of telling you that the url is too long is misleading. I will file a report on this. As far as a workaround, it looks like the only alternative is to shorten the length to within 230 characters. Do you agree?

    Tom

    Monday, April 6, 2015 8:55 PM
    Moderator

All replies

  • Hello Sergiodebst -

    Thanks for contacting Microsoft support. A support engineer will be in touch to assist further.

    Regards


    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Tuesday, March 31, 2015 11:32 PM
  • Hi Sergiodebst,

    I will take a look at this for you. On this forum, we are mainly focused on the file format itself and any (in this case) Excel behaviors that impact the meaning and implementation of the file format (binary and Office Open XML).  Having said that, my primary goal in investigating this issue is to determine if there is a problem with how Excel treats the XML in the connections.xml part you referred to since that part is specified in the ISO 29500 standard.

    It would help me to make that determination if you could send me an example of the resulting .xlsx from Excel 2013 that contains the truncated URL. If you can provide this, send the file to DocHelp at Microsoft dot com and reference the URL for this thread and my name.

    After that determination, I may be able to better understand the issue, identify potential behavior documentation needs, and/or file a report for Excel if needed. 

    Best regards,
    Tom Jebo
    Microsoft Open Specifications

    Wednesday, April 1, 2015 5:47 AM
    Moderator
  • And actually now that I reread your description, I think that you are saying the URL as stored in the package is complete. It's only the URL that you retrieve via the Interop interfaces, correct?

    Tom

    Wednesday, April 1, 2015 5:54 AM
    Moderator
  • Hi Tom,

    Yes, the xml is read correctly, and Yes I saw this error when I was working with Interop, but then I tried to reproduce this issue with Excel build-in functionality, so, in my desktop I've created a folder tree which full path is longer than 230 chars, then I've put a xml file in the last folder, I imported the xml file into Excel, save xlsx file, close it, and open it again, then the hability to update is lost, this also happens with internet URLs.

    Also I've tried with a path longer than 256 chars... If you use the OpenFileDialog to explore and select the long path xml file you will be informed about a problem with the import however if you accepts this information window, the import is done correctly. And if in the OpenFileDialog textbox you try to put the 256+ chars path directly you won't be able to open the file.


    Sergio

    Wednesday, April 1, 2015 11:09 AM
  • Hi Sergio,

    >>Also I've tried with a path longer than 256 chars...

    This is based on the limitation in Windows of 260 chars for a path length. The shell limits you but API's can create longer paths. Still Excel will not accept these and if it appears to work, it may or may not be correct. I would stick to the limit.

    >>I've created a folder tree which full path is longer than 230 chars, ...

    I tested this with a path over 230 but under 256 pointing to a sample xml file and it seems to work fine for me. The url attribute of the webPr element in the connections.xml part is not truncated at all.

    Based on this, it looks like you are running into some Excel behavior outside the scope of the ISO/IEC 29500 standard and Excel's use of it. I would recommend discussing this further with the folks on the Excel IT Pro forum (they mistakenly assumed the problem was related to Office Open XML when it is really about importing general xml data sets) or contacting Excel standard support. 

    Best regards,
    Tom Jebo
    Microsoft Open Specifications

    Monday, April 6, 2015 6:42 PM
    Moderator
  • Hi Tom,

    "The url attribute of the webPr element in the connections.xml part is not truncated at all. "

    I Agree.

    Sorry if I've expressed badly...

    I know that the connections.xml file is saved correctly, but now try to open the excel file and click to update.

    Monday, April 6, 2015 8:26 PM
  • Ah, I do see the problem. In the connections.xml, the url is correct. But Excel must not be reading the entire string from that attribute (i.e. truncating). I've checked [MS-OI29500] and it looks like for webPr's url attribute (or any of webPr's attr's) we don't have any length specifications. I will submit this as a request to update [MS-OI29500]. Excel is just following the limitation in Windows.

    Tom

    Monday, April 6, 2015 8:48 PM
    Moderator
  • And sorry, I meant to add that the fact that it reports an error instead of telling you that the url is too long is misleading. I will file a report on this. As far as a workaround, it looks like the only alternative is to shorten the length to within 230 characters. Do you agree?

    Tom

    Monday, April 6, 2015 8:55 PM
    Moderator
  • Thanks Tom!

    Well, as workaround, since I'm developing an Add-in, what I did is generate a CustomXMLParts binding XmlMAp with its source url and by the moment create my update buttons.

    Sergio

    Tuesday, April 7, 2015 7:54 AM