No announcement yet.
  • Filter
  • Time
  • Show
Clear All
new posts

  • putexcel and export excel return errors if the sheet name is longer than 31 characters, but the error messages are highly uninformative

    In Stata 14, this code works:
    putexcel set "example.xlsx", sheet("this name is too long for Excel") replace
    putexcel A1 = (2)
    but this code doesn't, and returns an r(691) error message that says "could not write to file."
    putexcel set "example.xlsx", sheet("this name is too long for Excel.") replace
    putexcel A1 = (2)
    The only difference is that the sheet name is 31 characters in the first piece of code, and 32 characters in the second code sample. Sheet names in Excel are capped at 31 characters, but the current error message is highly uninformative.

    Furthermore, the error message from "export excel" is even less helpful. This code
    sysuse auto, clear
    export excel "example.xlsx", sheet("this name is too long for Excel.") replace
    returns an r(9901) error message that says
          _xlshwritestrcol():  9901  Stata returned error
    export_excel_write_file():     -  function returned error
    export_excel_export_file():     -  function returned error
                     <istmt>:     -  function returned error
    I realize this is a symptom of Stata's wider problem with uninformative error messages, but it seems like it would be possible to perform basic validation in "putexcel" and "export excel" that would return an informative error message if the user tried to pass a sheet name of 32+ characters.

    I wasn't able to find any discussion of this in Stata's documentation, either, but this is a known, hard limit in the Excel format.
    Last edited by Michael Anbar; 20 Aug 2015, 08:56.

  • #2
    Is there a Power that forces you to use a very long sheet name in Excel? If not, don't.


    • #3
      Originally posted by Svend Juul View Post
      Is there a Power that forces you to use a very long sheet name in Excel? If not, don't.
      I often have to write sheet names that come from country and/or firm names, and some of these are quite long. Now that I've realized what was causing the error, I can use substrings to reduce the length of the name, but it took a while to figure out because the error message didn't give me a clue. Many Stata updates contain fixes to commands that returned uninformative error messages, so in general, this is an issue that Statacorp has addressed in the past. I don't think informative error messages can ever do *harm*, and they certainly help for debugging purposes.

      And frankly, I don't think it's too much to ask for a function that writes to a given format to actually perform validation for a parameter of the spec. I must say, I've never encountered a community where asking for error messages with even the most basic level of information is met with "don't do that" and/or "if you know the language it should be obvious" (as I've read on other posts). That's certainly a unique feature (rather, bug) of the Stata community.
      Last edited by Michael Anbar; 20 Aug 2015, 09:27.


      • #4
        I did not mean to be insulting, But I see that it can be understood that way.


        • #5
          Not to worry. I'm still hoping for a response from Statacorp or someone more knowledgeable on why this error message isn't helpful. If not for my benefit, then for others who might run into the same error (or if there are other parts of the Excel API that have similarly uninformative responses).


          • #6
            Some times the best way to report a problem is to email StataCorp technical support directly with full details, including a copy of files that reproduce the problem.
            Last edited by Nick Cox; 02 Sep 2015, 13:38.


            • #7
              Sorry for necroposting, but 31 chars is a limitation of Excel, not Stata.
              However, this limit is not documented, and the only suggestion is to use abbreviations, colors, special codes, etc.
              More tips at Excel MVPs pages, i.e.: