Limit decimal places in Excel automation

[antifashionpimp]

Hello everyone,

I was wondering if anybody could help with some Excel automation.

I have a form in Access, which exports the contents of a recordset to Excel in the following manner:

objWS.Range("A2").CopyFromRecordset rstData, 1000
gobjExcel.Range("A1").Select

What I want to do, is check the contents of the exported data in Excel automatically. If a cell contains a value with a decimal e.g. 123,456 then it must make the value only with 1 decimal place, e.g 123,46
Note: I do not want to truncate the values before I export them to Excel.

Is there any easy way of doing this? Please help as I only started automation recently.

Kind Regards,

Jean

 

[FLabrecque]

What I do is this:

- Open the xls file in VBA code.
- Format the column holding the numeric value to "# ### ##0.0#'.
- Save & Close the xls file.

This doesn't actually truncate the value. But it does appear to be truncated for those looking at it.

 

[fpendino]

Here's another option if you wanted to keep this within the coding part.

Since you are using the copyfromrecordset (which I haven't ever really used), you cannot format the data while you are outputting it. You can however format it within the recordset's sql statement.

Since I'm not sure what your sql statement is, here's an example.

sqlData = "SELECT tblEmpDept.Dept, Format([FTE],"0.00") AS Expr1 " & _
"FROM tblEmpDept;"

The FTE field in my case is a one digit number without decimals. Using the Format function that I included will format it with 2 decimal places no matter what. You may add more or less decimal places as needed.

 

[antifashionpimp]

Thanks guys, but I am more looking for a solution where I can do the formatting to Excel in Access vba code, without altering the SQL. The SQL will always be different, depending on what the user inputs into a form.

What I want is when I export to Excel, the user will only see 1 decimal place after the comma. I want this to happen when Excel displays the first time around.

Is this possible? Thanks again for your help.

 

[antifashionpimp]

I am thinking of using FLabrecque's solution in one of my queries.

You can however format it within the recordset's sql statement.

Since I'm not sure what your sql statement is, here's an example.

sqlData = "SELECT tblEmpDept.Dept, Format([FTE],"0.00") AS Expr1 " & _
"FROM tblEmpDept;"

However, this seems to change the data type of my formatted field. In other words, when I export the query results to Excel, the formatted field's value is left-aligned like text in the cell, and not right-aligned (number).

Can one with the Format function in the SQL also state that the number format should be kept, or can one maybe round to the nearest 1/10th to retain the number format?

Thanks again for your help!!!