Format a number field as text

[Tony Girgenti]

Hello.

I've developed a VB 6.0 program to import a fixed length fileds, ascii, text
file into a new Excel workbook.

I programmatically design a recordset and import the text file into the
recordset using these kinds of statements:

With casInputFileRecordset
.Fields.Append "ItemNumber", adChar, 16, adFldUpdatable

Do While Not casInputStream.AtEndOfStream
casInputFileLineString = casInputStream.ReadLine()
With casInputFileRecordset
.AddNew
!ItemNumber = Trim(Mid(casInputFileLineString, 73, 16))

There are other fields, just not listed for brevity sake.

Then in the business logic, i populate the worksheet with:

newExcelWorkSheet.Range("A2").CopyFromRecordset casInputRecordset

All this works and i'm very happy that i was able to complete such a task.

Sometimes the text file ItemNumber is just blank(12 spaces) and it's not
really a number, it can be alpha.

When i open the workbook, the field that contains ItemNumber has a little
tag next to it that says:
"The number in this field is formatted as text or preceded by an
apostrophe."

Very annoying. The ItemNumber column is programmatically formatted this
way.

newExcelWorkSheet.Columns("C").NumberFormat = "Text"

I also tried "Text" without the quotes. That displays the field with all
pound signs (#).

Also, the ItemNumber fields in Excel have twelve spaces in them including
the data. If the text file is blank for that field, it imports 12 spaces.

Does anybody know how to format that field so that it simply shows up as
text? and if it is blank, to make it an empty field not 12 spaces?

Any help would be gratefully appreciated.

Thanks,
Tony

 

[Jim Cone]

Tony,
Try changing "Text" to "@"...
newExcelWorkSheet.Columns("C").NumberFormat = "@"

 

[Tony Girgenti]

Hello Jim.

I'm not sure of what "@" is supposed to do, but that did not work.

Thanks,
Tony

 

[Jim Cone]

"@" is the designator that tells Excel to format cells as text.
"General" is the default format.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Tony Girgenti"
<tony(nospam)@lakesideos.com>
wrote in message
Hello Jim.
I'm not sure of what "@" is supposed to do, but that did not work.
Thanks,
Tony



"Jim Cone"
wrote in message