Format Excel Cell to 'Accounting' from VBA

[Soundsmith]

I am transferring data from an Access 2000 table to Excel 2000. The numbers export fine, but I need one column to be formatted in Accounting format with $. The code looks like:

Public Function XferXL() As Boolean
Dim xlapp As Excel.Application
Dim xlbook As Excel.workbook
Dim xlsheet As Excel.worksheet
Dim rsx As Recordset
dim thePath, theRpt,i, numrecs
' ---------- NOW OPEN EXCEL AND TRANSFER DATA -----
Set xlapp = New Excel.Application
thePath = Application.CurrentProject.Path & "\"
theRpt = thePath & "ca812.xls"
Set xlbook = xlapp.Workbooks.Open(theRpt)
Set xlsheet = xlbook.ActiveSheet
Set rsx = CurrentDb.OpenRecordset("tblTrialXL812")

'' NOW ADD THE DATA TO THE FIELDS

On Error GoTo err_XferXL

numrecs = rsx.RecordCount
rsx.MoveFirst
For i = 0 To numrecs - 1
    fnam = rsx.Fields(1).Value    'cell address
    fval = rsx.Fields(4).Value    ' fixed 2decimal value
    xlsheet.Range(fnam) = fval
    If Left(fnam, 1) = "F" Then
        xlsheet.Range(fnam).NumberFormat = _
    "_($* #,##0.00_);_($* (#,##0.00);_($* " - "??_)"
    End If
    rsx.MoveNext
Next i
xlapp.Visible = True

' I also tried to do it  this way...
'xlsheet.Columns("F").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* " - "??_)"

exit_XferXL:
rsx.Close
Set rsx = Nothing
Set xlsheet = Nothing
Set xlbook = Nothing
Set xlapp = Nothing
 Exit Function

Without the NumberFormat statement, it runs perfectly, but while all values are in the Access table as fixed decimal, 2 digit, one Excel column ("E&quot formats as integer, and works, the other ("F&quot is set as Accounting, but is overridden as Currency.
The NumberFormat statement generates a 'Type Mismatch' error. I copied the format string directly from Excel, and the statement code from their example. What's wrong?

Thanks.

David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[Stretchwickster]

Try changing your numberformat line to this:
.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

It has some extra bits on the end which may sort out your problem.

Hope this helps!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Soundsmith]

That worked perfectly! Thanks, Stretchwickster!

Dasher

David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[Stretchwickster]

A good practice to get into, in order to find out certain VBA code is to use the "Macro Recorder". Set it to Record, carry out the actions you desire. Click Stop then go to the VB Editor and look at the code it generated. It's a very useful tool!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[firstdivision]

Stretchwickster,

Wow, that is a good tip!

 

[Stretchwickster]

Glad to be of help! ;-)

Clive