WinBatch Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: RAK on March 28, 2014, 03:38:12 pm

Title: excel number formats
Post by: RAK on March 28, 2014, 03:38:12 pm
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
Title: Re: excel number formats
Post by: stanl on March 31, 2014, 07:12:09 am
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.
Title: Re: excel number formats
Post by: Deana on March 31, 2014, 08:07:02 am
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
Title: Re: excel number formats
Post by: RAK on March 31, 2014, 09:10:41 am
Yes the single quote works great when generating data - but in this case I am formatting data that already exists in the sheet.
Title: Re: excel number formats
Post by: Deana on March 31, 2014, 09:40:49 am
Did you see the code I posted and the resource links?
Title: Re: excel number formats
Post by: stanl on March 31, 2014, 09:48:59 am
Quote from: RAK on March 31, 2014, 09:10:41 am
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.
Title: Re: excel number formats
Post by: stanl on March 31, 2014, 09:56:36 am
Quote from: Deana on March 31, 2014, 09:40:49 am
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
Title: Re: excel number formats
Post by: Deana on March 31, 2014, 10:58:17 am
Quote from: stanl on March 31, 2014, 09:56:36 am
Quote from: Deana on March 31, 2014, 09:40:49 am
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


Title: Re: excel number formats
Post by: stanl on March 31, 2014, 01:19:53 pm
Quote from: Deana on March 31, 2014, 10:58:17 am
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.
Title: Re: excel number formats
Post by: RAK on April 01, 2014, 08:02:35 am
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.



Title: Re: excel number formats
Post by: stanl on April 01, 2014, 02:16:12 pm
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.