Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MSAccess output to Excel losing formatting of number
I have an MSAccess report whose property is =Sum([Amount]). I formatted it as
Standard with 2 decimal places and it looks perfect in msAccess. Examples: 100.00 0.00 35.42 When I use msAccess' OutputTo Excel feature, the amount column looks like this: 100 0 35.42 What am I doing wrong? I don't want the users to have to do additional work when it outputs to Excel. How can I make it output from msAccess properly? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MSAccess output to Excel losing formatting of number
Sounds like you are creating the Excel sheet on the fly. If there is o
formatting, then excel will just use a default format, such as General, which truncates zeros on the right of numbers (excel thinks they are irrelevant). You will have to control the formatting process from Access. Use this macro (in Access): Option Compare Database Option Explicit ' Use this to make sure your variables are defined ' One way to be able to use these objects throughout the Module is to Declare them here, and not in a Sub Private objExcel As Excel.Application Private xlWB As Excel.Workbook Private xlWS As Excel.Worksheet Sub Rep() Dim strFile As String strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls" €˜Of course, this is just an example; put the actual path to your actual file here€¦ ' Opens Excel and makes it Visible Set objExcel = New Excel.Application objExcel.Visible = True ' Opens up a Workbook Set xlWB = objExcel.Workbooks.Open(strFile) ' Sets the Workseet to the last active sheet - Better to use the commented version and use the name of the sheet. Set xlWS = xlWB.ActiveSheet ' Set xlWS = xlWB("Sheet1") With xlWS ' You are now working with the Named file and the named worksheet ' Your Excel code begins here...do the formatting here...you can even record a macro and make the process super easy!! End With ' Close and Cleanup xlWB.SaveAs xlSaveFile xlWB.Close xlapp.Quit Set xlapp = Nothing End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "slickdock" wrote: I have an MSAccess report whose property is =Sum([Amount]). I formatted it as Standard with 2 decimal places and it looks perfect in msAccess. Examples: 100.00 0.00 35.42 When I use msAccess' OutputTo Excel feature, the amount column looks like this: 100 0 35.42 What am I doing wrong? I don't want the users to have to do additional work when it outputs to Excel. How can I make it output from msAccess properly? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Losing all formatting in Excel 2007 files | Excel Discussion (Misc queries) | |||
macro to save current excel sheet only without losing formatting | Excel Programming | |||
Excel columns keep losing their formatting in 2007 Beta upgrade w. | Excel Discussion (Misc queries) | |||
Run An MSAccess VBA Function From Excel | Excel Programming | |||
pasting into an excel template without losing the formatting | Excel Discussion (Misc queries) |