Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a number field as text
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a number field as text
Tony, Try changing "Text" to "@"... newExcelWorkSheet.Columns("C").NumberFormat = "@" -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a number field as text
Hello Jim.
I'm not sure of what "@" is supposed to do, but that did not work. Thanks, Tony "Jim Cone" wrote in message ... Tony, Try changing "Text" to "@"... newExcelWorkSheet.Columns("C").NumberFormat = "@" -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a number field as text
"@" 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" 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 Tony, Try changing "Text" to "@"... newExcelWorkSheet.Columns("C").NumberFormat = "@" -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find text in free-format text field | Excel Discussion (Misc queries) | |||
Format Phone Number Field VBA | Excel Discussion (Misc queries) | |||
Pivot Table - NUMBER FORMAT option does not appear in field settin | Excel Discussion (Misc queries) | |||
ddd format to 'text' field | Excel Discussion (Misc queries) | |||
Number Field to Text or Words | Excel Discussion (Misc queries) |