The single quote format forcing should work in Excel 2013, but I don't have Excel2013 to test, so could you do a screenshot of the excel sheet created by this code (not using Gregs library, but that doesn't matter to show the format forcing works):
Code:
oExcel = CreateObject("Excel.Application")
oExcel.WorkBooks.Add()
oExcel.ActiveWorkbook.Cells(2,1).Value=['1]
oExcel.ActiveWorkbook.Cells(3,1).Value=[' 2]
oExcel.ActiveWorkbook.Cells(4,1).Value=[' 3]
oExcel.ActiveWorkBook.SaveAs('C:\test\some.xlsx') && adapt file name as you need.
You might need ActiveWorkBook.ActiveSheet to get to Cells. Using intellisense helps, if you start like this:
Code:
Local oExcel as Excel.Application
oExcel = CreateObject("Excel.Application")
Then typing oExcel and a dot will trigger intellisense to list all the properties and objects of the application object, like the Workbooks collection. You'll see whether ActiveWorkbook will already give access to Cells or only to the Sheets collection of a workbook, including the special ActiveSheet. Then you can also shorten the code using WITH... ENDWITH.
IIRC ActiveWorkBook.Cells() - or ActiveWorkBook.ActiveSheet.Cells() - addresses cells by (row, col), not by (col, row). So the code will fill three cells in column 1, rows 2-4 with 1-3 and increasing indentation, no single quote visible. Otherwise use Cells(1,2) (1,3) (1,4). The effect will show better, if you use a monospaced font, but it will show anyway. The interesting thing is just, whether the single quote shows or not.
Making that work with Gregs class clearly needs fixes, as I see in the latest thread posts, but first don't tell me you see the quote in the cells, when cell A1 is selected after opening the xlsx file with Excel 2013.
Aside from the difference it makes to generate XML instead of OLE automation of an Excel.Application object and how XML handles whitespace, there's nothing very special about how the single quote works, it just acts as a cell format modifier, forcing the cell value to be taken and displayed as is as text, not the usual "Standard" format, that means inferring the data type depending on the value, like interpreting dates, numbers, etc. which you don't want when you want a number treated as text. You can manually do the single quote check, too, as described in the Micrososft Answers thread. Enter a value like 'Name' with both single quotes into a cell, leave the cell, it shows Name' without the first quote. That's automatic and needs no special handling otherwise, also doing it with code instead of manually. If XML also treats a single quote character special, that would be an additional problem to XML whitespace handling, but there shouldn't be a problem with that, that would be impossible to solve in Gregs class library. it's just a matter to preserve this, too, like spaces. maybe an escape sequence would be necessary, but a single quote would only have a special meaning for attribute values within tags, in the XML of an XLSX workbook worksheet the values of cells are the text between an open/close XML tag pair, not within tags, so almost anything goes, the pointy brackets < and > are problematic, as they are tag delimiters, but almost anything else needs no special treatment from the perspective of XML.
There's definitely no problem with getting this character into a cell value and let it work its "magic" when using OLE automation of Excel.Application. Unless really Excel 2013 differs from that. I don't think so, as I remember having used this in newer Excel versions, still.
