0

In Version 2108 of Office 365, the character limit of msoPropertyTypeString seems to have increased beyond 255. I’ve stored a string over 500,000 characters long. Trying to find out if this will continue to be supported in future versions.

This fails in Version 2102, which truncates the string to 255 characters.

I haven’t found a place in Excel to store/persist a very long string (>100,000) so it was very convenient to use the CustomDocumentProperties. Hoping someone knows if this is official/permanent.

A good implementation: http://www.cpearson.com/excel/docprop.aspx

Microsoft’s documentation is lacking: https://learn.microsoft.com/en-us/office/vba/api/office.documentproperties.add

Alek X
  • 1
  • 1
  • One option is to embed a Word document in the spreadsheet, which will work if you or whoever is reading the sheet also has Word installed. The doc can be added via the `OLEObjects.Add` method, and the text accessed via `doc.Contents.Text`. Add the Microsoft Word Object Library to the Tools / References in VBA, then you can use `Word.Document`. NB: this might not be efficient on storage! The data for the document is stored in the Excel file. – DS_London Feb 17 '22 at 07:42
  • This is an option worth considering. All I need is a text file. I’m going to try a Power Query as well just for storage. – Alek X Feb 17 '22 at 16:30
  • I am certainly no expert on this ... but .xlsx files are XML, so if your Excel file doesn't have an macros, perhaps it would be possibly to augment the Excel schema and add you own tags to the XML, and store the string there? – DS_London Feb 18 '22 at 10:05

1 Answers1

0

Total number of characters that a cell can contain is 32,767 characters, see Excel specifications and limits. I suppose the same limitation can be applied to other areas like document properties. Note, you can split your long string into multiple strings/properties. See How to overcome custom document property size limit for more information.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
  • I’m aware of both of those options. It’s a headache to chop up a >100,000 character string into smaller chunks, and it affects performance. I’m just trying to confirm if Microsoft has permanently eliminated the 255 character limit on msoPropertyTypeString. Regular strings can be up to 2 billion characters: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary – Alek X Feb 17 '22 at 16:27
  • I'd suggest consider any other way of storing data other then custom properties on the document. May be a database with IDs embedded to the docs will be a better solution, or something like that. – Eugene Astafiev Feb 17 '22 at 17:07