DaneBigham
asked on
VBA Writeline string to .CSV shows as numeric
I'm using Access VBA to create a .csv file, including the following line:
...where rs("Field") = '001234'
So, the line writes out as FieldName,001234. Good.
However, when I open the .csv file in Excel, Excel thinks '001234' is a number, and displays 1234 (aligning right and truncating zeros). Is there anything in VBA I can do to show the six-character string when opening in Excel?
Thanks in advance.
objFile.writeline ("FieldName," & rs("Field"))
...where rs("Field") = '001234'
So, the line writes out as FieldName,001234. Good.
However, when I open the .csv file in Excel, Excel thinks '001234' is a number, and displays 1234 (aligning right and truncating zeros). Is there anything in VBA I can do to show the six-character string when opening in Excel?
Thanks in advance.
/gustav has suggested the simplest way.
Excel will view anything with a leading single quote as text and leave it alone.
But that puts a single quote in front of everything
Excel can be sometimes persuaded by concatenating a empty string together with numerics
objFile.writeline ("FieldName," & rs("Field") & "")
Now, you are creating a csv -- does it look right in notepad?
If it does, then it's Excel idiocy, and harder to deal with.
In Excel, you can format the column as General -- but the change will get lost when you close
You could also put in ~10 dummy lines of alphnumerics at the beginning of the column.
Excel might respect your text then.
Excel will view anything with a leading single quote as text and leave it alone.
But that puts a single quote in front of everything
Excel can be sometimes persuaded by concatenating a empty string together with numerics
objFile.writeline ("FieldName," & rs("Field") & "")
Now, you are creating a csv -- does it look right in notepad?
If it does, then it's Excel idiocy, and harder to deal with.
In Excel, you can format the column as General -- but the change will get lost when you close
You could also put in ~10 dummy lines of alphnumerics at the beginning of the column.
Excel might respect your text then.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
objFile.writeline ("FieldName," & "'" & rs("Field"))
/gustav