PDA

View Full Version : Solved: Number Format from access field into excel



slamet Harto
04-11-2009, 05:55 AM
Guys,

just quick question
a piece of my code doesn't work
Cells(I, 2).Value = Format([!1 - Prospect Identification], "#,##0.00")

i want to export all records in field 1-prosfect identification into excel. but it said "Type Mismatch"

Option Explicit
Dim Queryku As String

Sub ExportActivityReport()
Dim newrc As New ADODB.Recordset
Dim I As Long

Call Connect

Queryku = "select * from QueryLast"
ClearTemplate
I = 4
With newrc

Set RsCluster = Cn.Execute(Queryku)

If RsCluster.EOF Then
Exit Sub
End If


.Open Queryku, Cn, adOpenDynamic, adLockOptimistic, adCmdText

Do While Not .BOF And Not .EOF
Cells(I, 1).Value = Trim(!UserName)
'Format(Range("A1").Value, "#,##0.00")
'Format(Trim(!price), "Rp ###,###.#0")
'Format([BirthDate],'yyyy/mm/dd')
Cells(I, 2).Value = Format([!1 - Prospect Identification], "#,##0.00")
Cells(I, 3).Value = Format([![!2 - Prospect Qualification]], "#,##0.00")
Cells(I, 4).Value = Format([![!3 - Need Assessment], "#,##0.00")

I = I + 1
.MoveNext
Loop
.Close
End With

End Sub

Sub ClearTemplate()
Sheets("ReportActivity").Activate
Application.ScreenUpdating = False
Range("A5:J65535").Select
Selection.ClearContents
Range("A5").Select
Application.ScreenUpdating = True
End Sub
pls advise

slamet Harto
04-13-2009, 12:39 AM
I've got the answer.

Thanks & Rgds,
Harto

mdmackillop
04-14-2009, 05:42 AM
Can you post your solution?

slamet Harto
04-14-2009, 07:28 PM
Hi Malcom,
Surething, it is benefit to the other, right?

Cells(I, 2).Value = Format(![1 - Prospect Identification],
"#,##0.00")
Cells(I, 3).Value = ![2 - Prospect Qualification]
Cells(I, 4).Value = ![3 - Need Assessment]



thanks & regards,
Harto