Opening a text file in MS Excel - without data type conversion?
Joined: Jan 22, 2004
Sorry to interrupt with a technical question, but I don't know where else to ask this.
The situation is that my app creates a tab-delimited text file, which I want to 'open with' MS Excel. The problem is that one of the cells contains a long string of binary data (e.g., 01010101111111100000000010101010101000000000001111111110000011). I cannot find a way of making it display correctly though when I open the file in Excel.
When I open the file in Excel, it either shows as...
1010101111111100000000010101010101000000000001111111110000011 (with the leading zeroes missing - which is no good)
If my app writes the data into the file with an apostrophe in front (e.g., '01010101111111100000000010101010101000000000001111111110000011) then the apostrophe also gets displays in Excel.
How can I make it so that just the 01010101111111100000000010101010101000000000001111111110000011 shows when I open the file in Excel (preferably without having to modify loads of cell properties)?!
When you import the text file, you have the option of going through a wizard where you specify the format for each column. I don't know whether "binary" is one of those options (I rather doubt it but I haven't looked) but at any rate if you don't go through the wizard then you're stuck with whatever Excel chooses to do. As far as I know, anyway.
I had encountered similar problem in the past - I've put an apostrophe in the text file, but Excel kept displaying it when I opened that file. I discovered after much haggling that when I use the Replace form to replace an apostrophe with an apostrophe, it stops being shown in the cells. Unbelievable, but true.
A macro to do this can be probably easily recorded. I'm not sure whether this operation marks the file as modified. If it does and you don't want it, you could handle it in the macro.
Joined: Jan 22, 2004
Cheers for the replies, but I've found the solution now... I decided to open my file in MS Works Spreadsheet and I noticed that actually uses the " character (double apostrophe, rather than single apostrophe) at the start of text cells, so I've put that into my app and it's now sorted!!