excel number formats

[RAK]

I am dealing with hardware serial numbers in Excel - some are 20+ in length - I have been converting files to csv to avoid issues with Excel's desire to truncate and modify data. IN the end - I need to display the data in excel.. To avoid the issue I have been using templates with the column at issue, number formatted as 'Special' and left-aligned. This prevents excel from modifying the data.

I would like to skip the template part if I could figure out how to format columns as "Number Special".

I have been using the items below but cannot find info on number formats - anyone have info? Looking to format a column to "Number - Special" using COM before I place data in a new sheet.
thanks


   objWorkSheet.rows(1).font.bold = @true
   objWorkSheet.columns(1).ColumnWidth = 15
;   oWS.columns(1).HorizontalAlignment = -4131 ;left
;   oWS.rows(7).font.bold = @true
;   oWS.rows(7).HorizontalAlignment = -4108      ;center

[stanl]

Not sure about the COM formatting, but every time I have to use large numbers (serial #, workorder #) I insert them into Excel preceded by a ' . This essentially formats as text, lef-justified.

[Deana]

The code might look something like this:

Code: Winbatch

objXL = ObjectCreate("Excel.Application")
objConstants = ObjectConstantsGet(objXL)

objXL.Visible = @TRUE
objXL.WorkBooks.Open("C:\temp\data\test.xls")
objXL.Columns("A:A").Select

objSelection = objXL.Selection
objSelection.NumberFormat = "Number"        
objSelection.HorizontalAlignment = objConstants.xlLeft

objSelection = 0
objConstants = 0
objXL = 0

Message("Notice","Column A - format successfully changed")
Exit

Reference:
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel+Format~Excel~Column.txt
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel+Excel~-~constants.txt
http://msdn.microsoft.com/en-us/library/office/ff196401(v=office.15).aspx
http://msdn.microsoft.com/en-us/library/office/ff822120(v=office.15).aspx

[RAK]

Yes the single quote works great when generating data - but in this case I am formatting data that already exists in the sheet.

[Deana]

Did you see the code I posted and the resource links?

[stanl]

Yes the single quote works great when generating data - but in this case I am formatting data that already exists in the sheet.

Then just do a Replace in that Column in the Used Range with ' + exisiting data.

[stanl]

Did you see the code I posted and the resource links?

Deana;
 
Correct me if I am wrong, but your code will truncate to 0's after 15 digits.  Try entering 123456789123456789 into a cell, then format as number/left indent -  should come out 123456789123456000

[Deana]

Did you see the code I posted and the resource links?

Deana;
 
Correct me if I am wrong, but your code will truncate to 0's after 15 digits.  Try entering 123456789123456789 into a cell, then format as number/left indent -  should come out 123456789123456000

I see. Apparently, Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number, and changes digits after the fifteenth place to zeroes. To work around this behavior, format the cell as text, then type the numbers. The cell can then display up to 1,024 characters.

http://support.microsoft.com/kb/269370

To format columns as "Number Special". Maybe use objSelection.NumberFormat = "00000"

Reference:
http://www.mrexcel.com/archive/Formatting/19737.html

[stanl]

I see. Apparently, Excel follows the IEEE 754 specification

Which may be a moot point. The OP wrote that the data was already entered. If so (and it was all numbers > 15) then the damage would be done and formatting wouldn't help. By preceding with ' I suggested still is valid, but you would have to affix it to the underlying value.

[RAK]

objWorkSheet.columns(5).NumberFormat = "@"

This does it (text format)! - As stated by Deana - formatting as text must be applied to the area before the data is placed or it will not properly effect the data. This is working great for the results worksheet file I am creating.

There is still an issue with the .xls export file itself created by the web site (not mine). If the column is not formatted properly before it is saved - data is lost. All I can do here is tell them to format column "N" as 'Number Special' before they save. Since the data is already there - applying text formatting does not work but 'number - special' does cause excel to retain original digits when saved.

Formatting as text will sometimes work to restore original info for existing data depending on how it was saved previously. In cases where it does not work - formatting as Special may work when applied manually to restore original digits. Not sure of the logic behind this.

In this COM automation situation: objSelection.NumberFormat = "00000" did format the area as "Special" but did not have the desired effect on data placed.

My test user was complaining that the leading single quote ' I have been using for years in other solutions had to be removed when they were copy/pasting data as they are using them to perform web searches.

[stanl]

Yeah, that pretty much sums up the issues with Excel and huge numbers. Access can handle them as doubles displaying properly - would be nice if Excel had a double.