Link to home
Start Free TrialLog in
Avatar of DaneBigham
DaneBighamFlag for United States of America

asked on

VBA Writeline string to .CSV shows as numeric

I'm using Access VBA to create a .csv file, including the following line:

        objFile.writeline ("FieldName," & rs("Field"))

Open in new window


...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.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Yes, prefix a single quote:

    objFile.writeline ("FieldName," & "'" & rs("Field"))

/gustav
/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.
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial