Limit decimal places with Excel automation

[Jean]

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

[Rog]

Here's some code I am using, you can adapt it for your needs:

Dim xlApp As Object, sheet As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open ("c:\data\spreadsheet1.xls")
Set sheet = xlApp.ActiveWorkbook.Sheets(1)
sheet.Range("C1:C10").NumberFormat = "0.00"

[Jean]

Hi Rog

thanks, that makes sense, but I am still unsure how I would loop
through a range of cells, and check them to see if they need to be
changed.

Something I imagined to look like this:

for Each cell in Worksheet.Cells
if (cell has more than 1 decimal place) then
cell.NumberFormat ="0.0"
end if
next cell

Know what I mean? Can someone please give a hint as to how the syntax
should be?

Thanks again for your help.