Is it possible to leave a cell blank in an .xlsx?
I have a process by which vlookups populate a spreadsheet on a computer where macros are disabled. The resultant data is copied/paste values and saved in a new workbook. We have a problem in that cells with empty strings that were created by formula are being saved in the xml nodes as 0. When the data is read later by an external process over which we have no control, this is causing problems.
Similar questions have been asked often (examples, found, here) about creating or leaving blank cells, but none were specific enough to elicit an actual answer (more along the lines of "You don't want to do that, do this instead").
To replicate this, open a new Excel spreadsheet (2007-2016), place the formula ="" in B2, select the whole sheet, then copy and paste values. As expected, it will appear blank, and the xlLastCell will be B2 instead of A1. Even if one uses activesheet.usedrange it will not reset the xlLastCell to A1, because B2 does in fact have a value. When saving and examining the data in a text viewer (change the extension from .xlsx to .zip, extract, and open \xl\worksheets\sheet1.xml for those unfamiliar), you will see that in the sheetData node there is only one value, it is the node under B2, and it shows 0 (which is not equivalent to the empty string that had been pasted into that cell). While you are in there, for those of you who enterprising and creative souls who have already thought of using the apostrophe, you can test that in B3 and find that the resultant node is exactly the same, 0. A find replace doesn't work because, on this our example spreadsheet, when selecting "Find All" for empty strings, it finds only A1, which as you recall we haven't typed in (0 is not found at all).
Is there any way to actually leave the value node blank (or at least empty string) in the resultant xml, by this method?
Questions might be, why not use a .csv/flat file? Why not do it all yourself? Enable macros for this user? Why not spend 20-30 hours and write a .Net program to build the spreadsheet using xml and zipping it up when complete? Or simply, "You don't want to do that, why not do this instead"? These have been examined by committee and found sub-optimal. Are we just out of luck?
Is Excel simply unable to save empty strings as anything but the integer 0?
