Numberformat for Cells using Automation

[bavjean]

Hi there,

I have a procedure in Access that exports data to an Excel worksheet. Can
anyone here help me with the finer side of Excel VBA and it methods, please?

I have the following code so far:

Public gobjExcel As Excel.Application 'global variable
-------------------------------------------------------
Private Sub cmdExportieren_Click() 'actual procedure

Dim objWS As Excel.Worksheet

gobjExcel.Workbooks.Add
objWS.Range("A2").CopyFromRecordset rstData, 1000 'export
gobjExcel.Range("A1").Select

'code I want must go here

end sub

---------------------------------------------

So what I want in VBA is that I check the cell contents in a specific range,
and when the cell contents has a number in it, it must be a decimal value
with only 1 place after the comma.
Can this be written with an IF loop inside the VBA of Access?

I am grateful for any tips, or if someone can maybe point to a resource on
the web where this is explained.

Kind Regards,
Jean

 

[Patrick Molloy]

gobjExcel.Workbooks.Add
objWS.Range("A2").CopyFromRecordset rstData, 1000 'export

Dim text As String
Dim pos As Long
text = gobjExcel.Range("A1").Value
If IsNumeric(text) Then
text = Round(text, 1)
gobjExcel.Range("A1").Value = text
End If


Patrick Molloy
Microsoft Excel MVP

 

[Patrick Molloy]

also try

WITH gobjExcel.Range("B1:B1000")
.FormulaR1C1 = "=ROUND(RC[-1],1)"
.Value = .Value
END WITH
WITH gobjExcel.Range("A1:A1000")
.FormulaR1C1 = "=RC[1]"
.Value = .Value
END WITH


it would of course b easier if your recordset hald the data correctly
formatted!

 

[bavjean]

Patrick,

Will try both your suggestions at some time.
My recordset comes from a query which I have also tried to format the
numbers with round(), but no luck yet!

Thanks for the tips!

also try

WITH gobjExcel.Range("B1:B1000")
.FormulaR1C1 = "=ROUND(RC[-1],1)"
.Value = .Value
END WITH
WITH gobjExcel.Range("A1:A1000")
.FormulaR1C1 = "=RC[1]"
.Value = .Value
END WITH


it would of course b easier if your recordset hald the data correctly
formatted!

Dim text As String
Dim pos As Long
text = gobjExcel.Range("A1").Value
If IsNumeric(text) Then
text = Round(text, 1)
gobjExcel.Range("A1").Value = text
End If


Patrick Molloy
Microsoft Excel MVP