Hi Patrick Molloy,
Thanks for your reply. Actually, a run-time error 13: Type Mismatch is
coming,
which is not letting the format to be completed successfully on columns B &
C. When its applied on the date field column A, its formatting the data
successfully over there. I am calculating my fields from an sql joint query
which is using the column alias names, and so the number columns are
converted into text, when data is transferred into Excel, on which the code
is not being applied successfully. A green arrow mark is coming on the
column, on which message is coming to change the data from text to number.
Kindly guide how to remove the error and format the data successfully in the
B & C Columns. I'd much appreciate your guidance.

"Patrick Molloy" wrote:

you need to give us more information regarding the error message.
it is unlikely that this line of code
ws.Range("A1").CurrentRegion
will raise any errors

If you want just those two columns formatted then
ws.Range("B:C").NumberFormat = "0.00;(0.00)"

should work fine.
if cells contain TEXT, then of course, they don't get formatted, but this is
not an error.
if not, then you need to give us more details of the data or errors.




"Imran Ghani" wrote in message
...
Hi,
Thanks for your helping notes.
I have not yet been able to apply the command to the desired columns, as
there is an error message of data type mismatch. Can you please guide me
about the command which can convert data back to number from other
formats.
Regards,
Imran.

"Patrick Molloy" wrote:

change

to

ws.Range("B:C").NumberFormat = "0.00;(0.00)"

"Imran Ghani" wrote in message
...
Hi Patrick Molloy,
Thanks for your guidance. I have got my number data in column B and C.
Please guide me about how to give the above range in the following
ws.Range("A1").CurrentRegion
Regards,
Imran.


"Patrick Molloy" wrote:

when data is copied to excel from Access, they're just numbers, so
there
is
no formatting. you should open excel and use code to format the data
you can do this with Access VBA too

Sub tester()

Dim xl As Object
Dim wb As Object
Dim ws As Object

Set xl = CreateObject("Excel.Application")
''xl.Visible = True 'not required - use for testing

Set wb = xl.Workbooks.Open("C:\temp\abc.xls")
Set ws = wb.ActiveSheet

ws.Range("A1").CurrentRegion.NumberFormat = "0.00;(0.00)"

'cleanup - must have!!
Set ws = Nothing
wb.Close False
Set wb = Nothing
xl.Quit
Set xl = Nothing

End Sub


"Imran Ghani" wrote in message
...
Hi! I am exporting my report from ms access to ms excel with the
help
of
command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"qryunionpayinv", "D:\My Reports\banking.xls", True
I am not having the exported data in the desired currency format of
"000.00", nd a small green sign is also coming with the data. Kindly
guide
me
about how to export the data with the desired format, and of course,
w/o
the
green sign, as well, I also want to have the total of the number
field
at
the
end of the records.
Kindly guide me about how to achieve my task successfully in excel.
Regards,
Imran.