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.
DaneBighamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Yes, prefix a single quote:

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

/gustav
0
Nick67Commented:
/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.
0
Ejgil HedegaardCommented:
It happens when you open the csv file from Windows explorer.
Don't do that.
Open Excel and use the text import wizard on the Data tab.
Set the column to text, then the leading zeros will be kept.

When a new file is created, the table in Excel can be updated, and the once set format will be used.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.