formatting ms access to ms excel report

[Imran Ghani]

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.

 

[Patrick Molloy]

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

 

[Sam Wilson]

Hi,

You'll have to apply the formatting in your code yourself, you can't make
the export do it automatically. For instance:

Dim wb As Workbook
Set wb = Workbooks.Open("C:\Test.xls")

Dim ws As Worksheet
Set ws = wb.Worksheets("Sheet1")

ws.Columns("A:A").NumberFormat = "#,##0.00;"

Will open a file called test.xls saved at root C, and then in "Sheet1" will
set the number format to two decimal places in column A.

The green signs are likely to be caused by excel viewing the numbers as text
- if hover over them with the mouse it'll say "Number stored as text" - you
can use code to correct this too eg ws.range("a1").value =
cdbl(ws.range("a1").value)

Sam

 

[Imran Ghani]

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.

 

[Imran Ghani]

Hi Sam Wilson,
Thanks for your guidance. The code is working fine in the A Column of the
worksheet. But I'd like to format my columns B and C with the same code.
Kindly do guide me in this respect. I'd tried different options but not
succeeded.
Regards,
Imran.

 

[Sam Wilson]

Rather than use

ws.Columns("A:A").NumberFormat = "#,##0.00;"

use

ws.Columns("A:C").NumberFormat = "#,##0.00;"

 

[Patrick Molloy]

change
ws.Range("A1").CurrentRegion

to

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

 

[Imran Ghani]

Hi Sam Wilson,
Thanks for your guidance. A run-time error 13: Type Mismatch is coming,
which is not letting the format to be completed successfully. When its
applied on the date field column A, its formatting the data successfully over
there. Kindly guide how to remove the error and format the data successfully
in the B & C Columns.
Regards,
Imran.

 

[Imran Ghani]

Hi Patrick Molloy,
The command is working fine for the data in column A which is date field,
but when applied to columns B & C, its giving a run-time error 13: Type
Mismatch. Kindly guide about how to remove the error.
Regards,
Imran.

 

[Imran Ghani]

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.

 

[Imran Ghani]

Hi Sam Wilson,
Thanks for you kind help. Can you please also help about how to format the
number data as currency, and I'd appreciate much if you could also guide me
to have the sum of the values at the end of the data, programmatically,
whatever the number of the records is, if it is possible. Thanks in advance.
Regards,
Imran.

 

[Patrick Molloy]

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]

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.