Single Format export to xls

[mdmilner]

I'm looking for an elegant way to do this.
I'm using Format to calculate a value that gets added to a temporary table before the table values are exported to an xls as below.

asnTOT(y, 0, x) = Format(asnTOT(y, 0, x) / asnTOT(y, 1, x), "###0.00")

The two completely different methods I'm using to copy the field into xls are to use objSht.Cells() and objSht.Range().CopyFromRecordset.

Everything works except when the field ends up in the spreadsheet, I'm looking at 13 decimal places. Is there a good way to trim this down beforehand? As info, the field in the table (pre export) "appears" with only 2 decimal places as it should, but, that's just the displayed formatting.

Mike

 

[bat17]

I am not sure that the following is elegant but it should fix you at 2 decimal places

=(Cint((asnTOT(y, 0, x) / asnTOT(y, 1, x)*100)))/100

HTH

Peter

 

[mdmilner]

Well, I figured it would work but just like Format it doesn't.
I tried manually typing field values as xx.xx -- so it never had anything else and it still gets exported with 13 decimal places.

Think if I looked I'd find this as one of those documented bugs on MS's site.

Oops, 4th time I've edited this post.
I'm hoping the answer isn't to edit the xls after I've written it.
I may have to abandon the CopyFromRecordset method and send values cell by cell after using something like Format/Cint.

 

[bat17]

You may even get the problem that way too if is being generated at the Excel end. You may need to format the Excel cells by code after the transfer is done.

Peter